Using multiple IF statements

[mikeskins84]

Hi Everyone, this is my first post.

I'm trying to use the IF function to reduce a list of prices by a percentage, with varying percentages based on what the original price is.

My reduction percentages should be as follows:
Under 100 = 9% reduction
Over 100 = 11% reduction
Over 300 = 13% reduction
Over 500 = 16% reduction
Over 1000 = 20% reduction

My starting formula (all original prices are in column A):

=IF(A1>100,(A1*0.89),(A1*0.91))

This works for the over and under 100 percentages, but I can't work out how to add more IF's into the same formula. I've been trying for the last 2 hours to get additional IF's into the same line but I can't get it to work.

I'm no excel wizard. This is the most complicated thing I've ever attempted and could really use someone's help!

Thanks in advance!

[benishiryo]

hi mikeskins84. you should probably have a table somewhere. for illustration purposes, maybe in E1:E5 containing the minimum quantity to get the discounts. and in F1:F5 the discounts given. with that, you can do a formula:
=A1*(1-LOOKUP(A1,$E$1:$E$5,$F$1:$F$5))

[mikeskins84]

Thanks Benishiryo! It's using a solution and formula that I'm not familiar with, but it works like a charm!

Thanks again!

[Kevin UK]

Hi mikeskins84

Also VLOOKUP.

http://www.myonlinetraininghub.com/e...ulas-explained