Solved

# Excel Formula Help

Posted on 2011-05-10
270 Views
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
0
Question by:cpatte7372

LVL 81

Expert Comment

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

LVL 81

Expert Comment

Wait, that doesn't make sense either!

What is this?

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

Kevin
0

LVL 81

Expert Comment

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 Comment

zorvek,

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

Author Comment

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

LVL 81

Expert Comment

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

LVL 81

Expert Comment

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

=AND(CH2>CE2,CH2>CD2)

Correct?

Kevin
0

Author Comment

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 Comment

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 Comment

Kevin,

Why do you say the following will always be true?

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

LVL 81

Expert Comment

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

LVL 81

Expert Comment

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

I'm confused :-(
0

LVL 81

Expert Comment

Please explain with words what the new part is.

Kevin
0

LVL 81

Expert Comment

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 Comment

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 Comment

Kevin,

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

Cheers
0

LVL 81

Expert Comment

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

LVL 81

Expert Comment

Just did. Use:

=OR(CH2>CE2,CH2>CD2)

Kevin
0

Author Comment

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

LVL 81

Accepted Solution

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

Author Comment

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 Comment

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

LVL 85

Expert Comment

Rough guess:

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

0

LVL 10

Expert Comment

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

LVL 85

Expert Comment

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 Closing Comment

Cheers
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is aâ€¦
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.