Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel IF statement 1

Status
Not open for further replies.

metmanners

Technical User
Mar 10, 2008
24

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
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Most likely you have some text in L5 that looks like the number 9.5. For mathematical purposes text evaluates as the number 0.
 

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
 
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(K5:p5)
 
mintjulep said:
=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(K5:p5)
Can you elaborate on this?


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

=MAX(K5:p5) Does. K5:p5 means the RANGE of cells between K5 and P5. O5 is contained in that range.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top