Excel IF question 2

[erikhertzel]

I have a Excel question, have this formula:

IF(OR(E3>G3,F3>G3),F3

E F G H

0 35 41.44 FALSE
133 0 106.67 106.67



Basically, if E or F are greater than G (4%), I want it to return G -- which is working.

But if E or F is less than G, then I want it to return the number in E or F but right now it just returns FALSE.

What do I do any ideas?

 

[SkipVought]

Hi,
[tt]
=IF(OR(MAX(E3,F3)>G3),F3,MAX(E3,F3))
[/tt]


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[erikhertzel]

That's very close.

E F G H

133 0 106.67 0

But, I am getting a 0 for this H column now, which should be just the G value of 106.67

Here is my formula:

=IF(OR(MAX(E5,F5)>G5),F5,MAX(E5,F5,G5))

 

[SkipVought]

duh, sorry
[tt]
=IF(OR(MAX(E3,F3)>G3),MAX(E3,F3),G3)
[/tt]

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[erikhertzel]

No, my bad. I must not be explaining quite right.

Basically, if E or F are greater than G (4%), I want it to return G.

But if E or F is less than G, then I want it to return the number in E or F.

So, I have now:

E F G H

0 35 41.44 41.44
0 75 69.15 75
133 0 106.67 133

So for #1, I am trying to get the value of 35 since it's less than 41.44

For #2, I am trying to get value of 61.15 since it's less than E or F.

For #3, I am trying to get value of 106.67 since E is greater than G.

I hope I make more sense, this is kind of confusing.

 

[AnotherHiggins]

This is the formula you wrote in your original post:

[tab]IF(OR(E3>G3,F3>G3),[!]F3[/!]

That returns the value from F (zero), not the value from G.

But your criteria in your first post aren't logical. You say,

...if E or F are greater than G (4%), I want it to return G -- which is working.

But if E or F is less than G, then I want it to return the number in E or F but right now it just returns FALSE.

I suspect that one of those ORs should be an AND. What happens if E<G and F>G? You say "then I want it to return the number in E or F". Which number, E or F?!?! The lowest? The highest?

What would you want returned in this scenario:[tt]
E F G H
49 51 50 0[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[erikhertzel]

John,

I see what you are saying:

In E or F one of those values is always going to be a zero value, so I always want the value that is greater than zero in either E or F compared against G and then decision from that.

Thanks for your clarification.

 

[AnotherHiggins]

I think the confusion in Skip's first reply came from your using F in your original post instead of G.

[tab]=IF(MAX(E3,F3)>G3,G3,MAX(E3,F3))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[erikhertzel]

Yes, I think you are right, thanks to both of you.