Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Excel Formula Help

Hi Experts,

Think this should be rather straight forward so I haven't included the spreadsheet as an illustration.

Can someone please show me how to add another OR statement to a formula.

So at the moment I'm using the following formula already with an OR statement:

=IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

I would like to add the additional OR statement:

(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)

If you need the spreadsheet let me know, however I think someone will be able to assist without it.

Cheers

Carlton
0
cpatte7372
Asked:
cpatte7372
  • 12
  • 12
  • 2
  • +1
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
This doesn't make sense:

=IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

I think you mean:

=IF(OR(CH2>CE2, CH2>CD2), ResultIfEitherTrue, 0)

Then adding the third OR condition:

=IF(OR(CH2>CE2, CH2>CD2, CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5), ResultIfEitherTrue, 0)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Wait, that doesn't make sense either!

What is this?

(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Perhaps you want to test for four conditions?

Then this makes sense:

=IF(OR(CH2>CE2, CH2>CD2, (CH2-CE2)>(CF2-CH2), (CE2-CG2)>(CH2-CE2)*1.5), ResultIfAnyTrue, 0)

You are looking for any of the following:

CH2>CE2
CH2>CD2
(CH2-CE2)>(CF2-CH2)
(CE2-CG2)>(CH2-CE2)*1.5

Is that right?

Kevin
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cpatte7372Author Commented:
zorvek,

Sorry for the confusion. About to show you the spreadsheet. Just a sec...
0
 
cpatte7372Author Commented:
Expert,

As you will see in column CP cell 2 we have the formula:

=IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

I would like to have an additional OR statement which goes like this:

 OR CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)

So we end up with two OR statements


EE20-Day-Low.xlsm
0
 
zorvek (Kevin Jones)ConsultantCommented:
The first formula doesn't make sense. This part:

   OR(CH2>CD2, TRUE, FALSE)

will always be TRUE. So you can write the formula as:

   =IF(CH2>CE2, TRUE, 0)

Now how do you want to incorporate this?

   OR CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)

Perhaps like so:

   =IF(OR(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5), TRUE, 0)

This will return TRUE if any of these are true:

    CH2>CE2
    (CH2-CE2)>(CF2-CH2)
    (CE2-CG2)>(CH2-CE2)*1.5

or zero if none are true.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
From the header "Condition3: CH > CE & CH > CD" it appears you want this:

   =AND(CH2>CE2,CH2>CD2)

Correct?

Kevin
0
 
cpatte7372Author Commented:
Kevin,

CH > CE & CH > CD should read CH > CE or CH >CD a typo.

Does that change the syntax of your formula:

OR CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)  ??
0
 
cpatte7372Author Commented:
Kevin,

I should point out that an expert called Dave originally compiled this formula for me

=IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

And it does appear to be working....

I need to additional OR statement.

However, if you think its not correct I will take your advice.
0
 
cpatte7372Author Commented:
Kevin,

Why do you say the following will always be true?

=IF(CH2>CE2, TRUE, 0)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Whoever Dave is they don't know what they are doing. This:

   =IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

is the same as:

   =IF(CH2>CE2, TRUE, 0)

which is NOT this:

   CH > CE or CH >CD

But this is:

   =OR(CH2>CE2,CH2>CD2)

I don't understand this:

   OR CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
>Why do you say the following will always be true?

I didn't say that. I said:

"The first formula doesn't make sense. This part:

   OR(CH2>CD2, TRUE, FALSE)

will always be TRUE."

Kevin
0
 
cpatte7372Author Commented:
I'm confused :-(
0
 
zorvek (Kevin Jones)ConsultantCommented:
Please explain with words what the new part is.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Look at your workbook. Column CP has nothing but TRUE and 0. No FALSE. That means that the formula:

   =IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

is returning TRUE or 0. That's because this part:

   OR(CH2>CD2, TRUE, FALSE)

is always returning TRUE. The OR function looks at each part and if any of the parts is TRUE then the function returns TRUE. The second parameter is TRUE therefor the function will always return TRUE.

Kevin
0
 
cpatte7372Author Commented:
OK,

I'll try to explain CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5) in a second.

First, should I replace  =IF(CH2>CE2, OR(CH2>CD2, TRUE, FALSE), 0)

with

  =OR(CH2>CE2,CH2>CD2)   ??
0
 
cpatte7372Author Commented:
Kevin,

Point taken, 'it is returning nothing but TRUE..... so can you guide me as to what to replace it with?

Cheers
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes. If the column header is correct then that is what you want for a formula. That header being "Condition3: CH > CE OR CH > CD".

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Just did. Use:

  =OR(CH2>CE2,CH2>CD2)

Kevin
0
 
cpatte7372Author Commented:
Kev,

That's working now (not all TRUE statements). Cheers

Just let me know if you understand the formula without the OR statement, i.e. by itself? If not, I'll dig out my past questions for a explanation

CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5)

0
 
zorvek (Kevin Jones)ConsultantCommented:
What you have is three comparison statements separated by commas. It doesn't mean anything to me.

CH2>CE2
(CH2-CE2)>(CF2-CH2)
(CE2-CG2)>(CH2-CE2)*1.5

What do you want to do if all three are true?

What do you want to do if any of the three are true?

Kevin
0
 
cpatte7372Author Commented:
OK, if all three are true I would like are TRUE statement to be added to:

 =OR(CH2>CE2,CH2>CD2)

so that it becomes something like  =OR(CH2>CE2,CH2>CD2),(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5))

Basically, I end up with three OR statements and if either one of them is True then I get a TRUE in the column.

I hope I'm explaining myself correctly...
0
 
cpatte7372Author Commented:
Kev,

I going to stick with the corrections that you found. That has helped me out a lot - I was missing a number of opportunities.

Cheers mate....
0
 
Rory ArchibaldCommented:
Rough guess:

=OR(CH2>CE2,CH2>CD2,AND(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5))

0
 
gavsmithCommented:
Based on the information provided (still a little confused on what you are trying to acheive though) rorya formula is also what I arrived at, however...

There doesn't seem to be any point in checking the bold condition below:

=OR(CH2>CE2,CH2>CD2,AND(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5))

this is also checked at the begining of the OR statement:

=OR(CH2>CE2,CH2>CD2,AND(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5))

therefore if it is true then the final result will be true regardless of the rest of the AND statement.
0
 
Rory ArchibaldCommented:
True - it can therefore be shortened to:

=OR(CH2>CD2,AND(CH2>CE2,(CH2-CE2)>(CF2-CH2),(CE2-CG2)>(CH2-CE2)*1.5))
0
 
cpatte7372Author Commented:
Cheers
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 12
  • 12
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now