Excel - IF AND commands 3

[kdr1974]

Hi

I have a sheet which calculates a value between 0-29 based on other cells in the sheet.

What I want to do is have a condition returned dependent upon the number range, for example, 0-5 returns the word "green", 6-8 returns "amber" and 9-27 returns "red".

I can do this over three individual cells using the IF() and the quote marks but I cannot get everything into one cell - am I right in thinking I need the nested IF command and if so, how do I do it?

Thanks in advance
Kyle

 

[bluedragon2]

=IF(A1<=5,"Green",IF(A1<=8,"Amber",IF(A1<=27,"Red","Not in Range")))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[kdr1974]

Many thanks - that's exactly what I wanted!

 

[SkipVought]

Hi,

You might consider using a lookup table like this...
[tt]
Val Color
0 green
5 amber
8 red
28 << this cell has a SPACE value
[/tt]
with a formula like this, using NAMED RANGES...
[tt]
=IF(J1<0," ",INDEX(Color,MATCH(J1,Val,1),1))
[/tt]
where J1 is the lookup value.

The reason is, that merely with a change of values in the lookup table, you and easily change the bounds and return values, without having to change any formulas.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kdr1974]

Thanks Skipvought, will bookmark that one!

 

[Gavona]

Hey kdr1974, the "proper" way to thank skip is to click on [blue]
Thank SkipVought
for this valuable post!
[/blue]

Gavin

 

[GSCaupling]

=IF(A1<=5,"Green",IF(A1<=8,"Amber",IF(A1<=27,"Red","Not in Range")))

Thanks blue. I learned something (imagine that!)

At first glance, I thought you had erred. After all, "3" matches all three "ifs." How would it know which "then" to obey? After playing with it, I realized they are progressive. It appears, going from left to right, the formula executes upon the first met criteria. Cool.

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[SkipVought]

There really needs to be another criteria, >0 and <=5

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}