Nested If with Or

[Mebs]

Can someone help me with if statement with or condition in it. Basically what I am trying to achieve is to band the percentages in to 9 categories.

Cell Q3 contains formula which is represented by a % (eg O3/N3 as a %)
Cell N3 containes a value (Order value £1400)
Cell O3 contains a value (Tender value £1250)

So in cell V3, I need a nested if statement with or in it to do;
if Q3 is = 0 then "e. Broke Even"
if Q3 is greater than -0.01% or less/equal to -10.0% then "d. Between 0% and 10% Loss"
if Q3 is greater than -10.01% or less/equal to -25.0% then "c. Between 10% and 25% Loss"
if Q3 is greater than -25.01% or less/equal to -50.0% then "b. Between 25% and 50% Loss"
if Q3 is greater /equal to-50.01% then "a. Over 50% Loss"
if Q3 is greater than 0.01% or less/equal to 10.0% then "f. Between 0% and 10% Gain"
if Q3 is greater than 10.01% or less/equal to 25.0% then "g. Between 10% and 25% Gain"
if Q3 is greater than 25.01% or less/equal to 50.0% then "h. Between 25% and 50% Gain"
if Q3 is greater /equal to 50.01% then "i. Over 50% Gain"

On the above example (1400 / 1250) = -150, as a percentage its loss of 12%. Therefore the banding should be, c. between 10% and 25% loss.

 

[doublej_jj]

=if(Q3=0,"Broke Even",if(Q3>=.50,"I.",if(Q3>.25,"'H",if(Q3>.10,"G",if(Q3>0,"F",if(Q3>-.1,"D",if(Q3>-.25,"C",if(Q3>-.50,"B","A"))))))))

I only used the letter (not the words) associated with each result, You can add the words. Notes:

* Do the = statement first
* Then go from your largest to smallest condition
- IF > 50% This grabs everything greater than 50%
- IF > 25% This grabs 25% to 50%
- IF > 10% This grabs 10% to 25%
- IF > 0% This grabs 0% to 10%
- IF > -10% This grabs -10% to 0%
- IF > -25% This grabs -25% to -10%
- If > -50% This grabs -50% to -25%
- Everything else This grabs everything less that -50%

Hope this helps.

Jeff

 

[Mebs]

Thanks Jeff that worked a treat. What I don't get is 'IF(Q3>0.25,"h. Between 25% and 50% Gain, etc, how come your don't have to specify the range, that is 25% to 50%?

 

[doublej_jj]

By ordering them from greatest to least, you take care of the top part of your range in the previous condition.


- If you value in Q3 is 35%
* First, is it zero? No move to next one
* Second, is it >50%? No, move to next one
* Third, is is > 25%? Yes, type your words and stop
- In the Second step, I've ruled out everything greater than 50%, or essentially created my upper limit.

Hope that makes sense. I always like a person who wants to learn, not just have the answer.

Jeff

 

[Scott Huish]

Have a look at the LOOKUP function:

=IF(Q3=0,"Broke Even",LOOKUP(ABS(Q3),{0,0.1,0.25,0.5},{"Between 0% and 10%","Between 10% to 25%","Between 25% to 50%","Over 50%"}))&IF(Q3>0," Gain"," Loss")