Nested If statements

[Excel for President]

I am currently struggling with a formula I have in my excel. There is a number in cell F11 that is between 1 and 5 (it varies based on the information entered). I am trying to write a formula that will say if the number is less than 2, then say Bronze. If it is 2-3, then say Silver. If it is 3-4 then say Gold. Finally, if it is greater than 4, then say Platinum. I've tried using: =IF(F11>4,"Platinum",=IF(F11>3,"Gold",=IF(F11>2,"Silver",=IF(F11>1,"Bronze",0)))) and =IF(F11<2,"Bronze",=IF(AND(F11>2,F11<3),"Silver",=IF(AND(F11>3,F11<4),"Gold",=IF(F11>4,"Platinum")))) but had no luck with neither formula. I thought both formulas would work but apparently I was sadly mistaken. Is there something I'm missing? Any help is much appreciated!

 

[mole999]

real numbers or text numbers?

 

[tygrrboi]

The conditions you gave above are not really clear.... what if the number is 3... is it silver or gold?
If you are inputting integers (no decimal places) then this will work for 1 = bronze, 2 = bronze, 3 = silver, 4=gold, 5 = platinum....
=IF(AND(F11>0, F11<6),CHOOSE(F11, "Bronze","Bronze","Silver","Gold", "Platinum"),"not a number between 1 and 5...")

If you are using decimals...

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}</style>=IF(AND(F11>=1, F11<=5),CHOOSE(ROUNDUP(F11,0),"Bronze", "Bronze", "Silver", "Gold","Platinum"),"not a number between 1 and 5...")

 

[Excel for President]

Its could be any number between 1-5. So 1.23, or 4.3, or 3.2, etc.

 

[tygrrboi]

you're not accounting for conditions when the value is 3.0 or 4.0...

If it is 2-3, then say Silver. If it is 3-4 then say Gold.

What if it is exactly 3?

 

[Excel for President]

Also for clarification, I want 1-2 to be Bronze, 2.0000...1 to 3 to be Silver, 3.000...1 to 4 to be gold, and 4.000...1 and up to be platinum.

 

[tygrrboi]

The formula I gave earlier for decimals should work then. Give it a try.

 

[Excel for President]

I gave your formula for decimals a try, and even though I currently have the number 1.45, I am receiving "not a number between 1 and 5".

 

[tygrrboi]

Maybe the numbers are actually text?

What if you replace all of the F11 with --F11?

=IF(AND(--F11>=1, --F11<=5),CHOOSE(ROUNDUP(--F11,0),"Bronze", "Bronze", "Silver", "Gold","Platinum"),"not a number between 1 and 5...")

 

[Excel for President]

Same result