Excel 2007 Conditional Formula

[PlanetOrb]

Hey all,

I am using the following formula for a rating system based on the Value of a Cell (A1).

=IF(A1=5,4,IF(A1=5,4,IF(A1=4,3,IF(A1=3,2,IF(A1=2,1,IF(A1=1,0,))))))

It works great until I try and change the first part of the formula:

=IF(A1<=5,4,IF(A1=5,4,IF(A1=4,3,IF(A1=3,2,IF(A1>=2,1,IF(A1=1,0,))))))

Adding the Greater Than sign to the first part of the formula:

=IF(A1<=5,4,

causes it to return a 0 for anything greater than 10. Can anyone give me a pointer on this one?

Thanks!

Mike

 

[SkipVought]

"Adding the Greater Than sign..."

you did not add a GREATER THAN sign. You added a LESS THAN sign.

"...causes it to return a 0 for anything greater than 10"

The largest test value is 5. Where does 10 come from??? Anything greater than 5 is undefined in your formula.

I'd suggest using a table instead, with an inexact lookup. Check out VLOOKUP or MATCH.

Or just change < to >



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[PlanetOrb]

My bad, I had shortened the formula down to 5 and under for clairity when I posted.

Thanks for pointing out the problem Skip, I must be going totally brain dead on this one. Works fine now.

Mike