Nested If-Then statement with number range.

[ladeewzl]

This is what I want to do.

If cell K184 is less than 165, multiply cell K182 by .5
If cell K184 is 166-300, multiply cell K182 by .55
If cell K184 is greater than 300, multiply cell K182 by .6

This is the formula that has returned the most results so far...
=IF(K184<165, K182*0.5, IF(OR(K184>165,K184<300), K182*0.55, IF(K184>300,
K182*0.6)))

It works for the first and second IF statements. However, if cell K184 is
more than 300 it still multiplies it by .55. What am I missing?

 

[Elkar]

Try this:

=IF(K184<=165,K182*.5,IF(K184<=300,K182*.55,K182*.6))

You don't need to specify so many conditions, since the FALSE portion of an
IF Statement only evaluates if the condition is false. So, by the time it
gets to K182*.6, we've already established that K184 is greater than 300.

HTH
Elkar

 

[ladeewzl]

TYVM!!!

Try this:

=IF(K184<=165,K182*.5,IF(K184<=300,K182*.55,K182*.6))

You don't need to specify so many conditions, since the FALSE portion of an
IF Statement only evaluates if the condition is false. So, by the time it
gets to K182*.6, we've already established that K184 is greater than 300.

HTH
Elkar

 

[David Biddulph]

Your problem is that K184>300 satisfies the condition K184>165, and
therefore satisfied the OR condition, so gives the 0.55 result. You
probably intended AND rather than OR, but see below.

In your text description you haven't defined a result for K184 between 165
and 166, but you could start with something like
=K182*IF(K184<165,0.5,IF(K184<=300,0.55,0.6))
You may want to vary the < and <= conditions.
Remember that if you've tested for <300 you don't afterwards need to test
for >= 300, and similarly with the 165 condition.

In your formula, if you change OR to AND, you haven't given a result for
K184=165 or K184=300, so as you haven't given an alternative result for the
last IF your result would be the Boolean FALSE.

 

[Ashish Mathur]

Hi,

Try this

=vlookup(K184,A2:C4,3)*K182. In A2:A4, you have 0,166 and 300. In B2:B4,
you have 165,300 and blank. In C2:C4, you have 0.5, 0.55 and 0.6

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com