Excel IF statement 1

[metmanners]

In cell M5 I have the following formula:

=IF(K5>L5,K5,L5)

The formula should enter the value from K5 into cell M5 if K5 is greater than L5.

If K5 is less than L5, the formula should enter the value from L5 into M5.

I have set up an example in the worksheet so that the value in K5 is 7 and the value in L5 is 9.5.

The formula returns 7 into cell M5 which is wrong because 9.5>7.

Any ideas?


metman

 

[SkipVought]

hi,

Your formula is correct and returns 9.5 for me.

Check to see what your cell formatting in K5 & L5 is, and if you have leading or trailing SPACES.

Personally, I would use ...
[tt]
=MAX(K5,L5)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

Most likely you have some text in L5 that looks like the number 9.5. For mathematical purposes text evaluates as the number 0.

 

[N1GHTEYES]

Why not just use:
=MAX(K5:L5)

Tony

 

[metmanners]

Thank you: SkipVought & N1GHTEYES

Either one of these two formulae work:

=MAX(K5,L5)
=MAX(K5:L5)

Ha ---- This: IF(K5>L5,K5,L5) also works if the dummie puts in the preeminent = sign in front of the IF.

metman

 

[mintjulep]

Either one of these two formulae work:

=MAX(K5,L5)
=MAX(K5:L5)

In this particular case, yes they will have the same result.

It's important to note the fundamental difference though.

=MAX(K5,L5) returns the max from an ARRAY of values (in this case the array consists of only 2 elements).

=MAX(K5:L5) returns the max from a RANGE.

=MAX(K5, L5, M5, N5, P5)

could return something different than

=MAX(K55)

 

[metmanners]

=MAX(K5,L5) returns the max from an ARRAY of values (in this case the array consists of only 2 elements).

=MAX(K5:L5) returns the max from a RANGE.

This helps

=MAX(K5, L5, M5, N5, P5)

could return something different than

=MAX(K55)

Can you elaborate on this?


metman

 

[mintjulep]

=MAX(K5, L5, M5, N5, P5) Does not consider what is in cell O5

=MAX(K55) Does. K55 means the RANGE of cells between K5 and P5. O5 is contained in that range.

 

[metmanners]

Thanks Mint, Skip, and Eyes!

metman