Returning word if between two values (2 Viewers)

[DavidRS]

Can this be done?

I trying to get Excel to return either Low, Medium or High based on the result of another function. This is for a risk management sheet. If the cell result is under 40%, I want it to show "Low" as the result; if the value is between 40% and 87%, the return is "Medium". Any other value would be "High".

I've tried nesting if functions but I have managed to get the result i wanted so long as the "medium" value is exactly a figure (for example 50%). The problem is that the original function identifed a "Low" result but everything else is medium. The function is:

=IF($AD7<0.5,"Low",IF($AD7>=0.5,"Medium",IF($AD7<0.85,"Medium",IF($AD7>0.85,"High","ERROR"))))

where AD7 is a simple math sum to work out the % of one cell over a maximum value.

I've tried searching the forum and the web and I have had no luck except for finding a SQL function for use within Access.

I'm running Office07 on a Vista system.

Many thanks in advance

David

 

[namliam]

IF($AD7<0.5,"Low",
IF($AD7>=0.5,"Medium",
IF($AD7<0 .85,"Medium",
IF($AD7>0.85,"High","ERROR"))))

Your second IF says >= 0.5 then Medium, which means that 0.90 will also be >= 0.50 and return Medium... You either need to "reformat" your formula like so:
IF($AD7<0.5,"Low",
IF($AD7>0.85,"High","Medium"))
Getting the High and Low first, anything left over is Medium

or Use AND...
IF($AD7<0.5,"Low",
IF(And($AD7>=0.5, $AD7<0 .85),"Medium",
IF($AD7>0.85,"High","ERROR"))))
To actually trap only the Medium when the value is between 0.50 and 0.85

Note: In this case NOTHING will happen if the value IS 0.85 because there is no = sign anywhere.

Good luck

 

[DavidRS]

Your second IF says >= 0.5 then Medium, which means that 0.90 will also be >= 0.50 and return Medium... You either need to "reformat" your formula like so:
IF($AD7<0.5,"Low",
IF($AD7>0.85,"High","Medium"))
Getting the High and Low first, anything left over is Medium

You're a star.
Thank you Mailman - It's not as if I haven't done this before. I just couldn't see the wood for the trees.

Of course, now all my conclusions are out of the window! Never mind, I'll blag it.

Cheers

 

[namliam]

Never mind, I'll blag it.

blag it???

Happy to help

 

[DavidRS]

blag it???

Be creative with the truth; off the cuff; smoke and mirrors i.e. lie