I think you have your less than (<) and greater than (>) symbols mixed up (in
your comments anyways). So this is what I assume you meant...
If x is less then 8, then 0
If x is greater than 8, and less than 15, then 0.5*x
If x is greater than 16, and less than 49, then 0.75*x
if x is greater than 50, x
Your formula has a couple of errors.
First, "Q5>16<49" and "Q5>8<15" have no logical meaning in excel. I
believe both will return FALSE no matter what the value of Q5 actually is.
You can replace "Q5>16<49" with something like "AND(Q5>16,Q5<49)". So you
"could" use the following formula...
=IF(Q5>50,E5,IF(AND(Q5>16,Q5<49),E5*0.75,IF(AND(Q5>8,Q5<15),E5*0.5,IF(Q5<8,0))))
but this really isn't very efficient, and still has another problem.
Second, you might get wrong results because the formula produces 0 if Q5
equals 49, 50, 15, 16, and 8. For ranges you typically want to use > in one
part of the formula and <= in the next, like this.
=IF(Q5>50,E5,IF(AND(Q5>16,Q5<=50),E5*0.75,IF(AND(Q5>8,Q5<=16),E5*0.5,IF(Q5<=8,0))))
but in your example the ranges all meet. So you really don't need the AND
functions at all. So the formula could be.
=IF(Q5>50,E5,IF(Q5>16,E5*0.75,IF(Q5>8,E5*0.5,0)))
notice the second statement does NOT include "Q5<=50". This is because at
this point in the formula Q5 must be less than or equal to 50, or else it
would trigger the first test of "Q5>50". Notice also the last statement has
been omitted if Q5 is not greater than 8, then it must be less than or equal
to 8.
You can make it slightly smaller still by extracting the E5 in front of
the IF statement.
=E5*IF(Q5>50,1,IF(Q5>16,0.75,IF(Q5>=8,0.5,0)))
This formula is pretty good and efficient, but many people (like myself)
don't like nested IF's and avoid them like the plague. Logical values (TRUE
and FALSE) are converted to 1's and 0's when you try to apply a mathatical
operator to them. So, you can use the following formula (though it might be
confusing to newer users).
=E5*((Q5>8)/2+(Q5>16)/4+(Q5>50)/4)
