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
cpatte7372Asked:
Who is Participating?
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.