# 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)

(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
###### Who is Participating?

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

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

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

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

Author Commented:
zorvek,

Sorry for the confusion. About to show you the spreadsheet. Just a sec...
0

Author 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

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

ConsultantCommented:
From the header "Condition3: CH > CE & CH > CD" it appears you want this:

=AND(CH2>CE2,CH2>CD2)

Correct?

Kevin
0

Author 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

Author 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

Author Commented:
Kevin,

Why do you say the following will always be true?

=IF(CH2>CE2, TRUE, 0)
0

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

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

Author Commented:
I'm confused :-(
0

ConsultantCommented:
Please explain with words what the new part is.

Kevin
0

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

Author 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

Author Commented:
Kevin,

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

Cheers
0

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

ConsultantCommented:
Just did. Use:

=OR(CH2>CE2,CH2>CD2)

Kevin
0

Author 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

Author 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

Author 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

Commented:
Rough guess:

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

0

Commented:
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

Commented:
True - it can therefore be shortened to:

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

Author Commented:
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.