Hi Guys Thanks for responding sorry I didn't reply sooner I have been out of the country.

I am getting myself a little confused so if I try and type out the formula I am wanting to do maybe someone could help. I apologize but this is new stuff to me really

Basically I have some discount rates if someone buys over a certain number of units they are as follows

1000-10000 no discount
11000-25000 5% discount
26000-50000 10% discount
Over 50000 20%
Over 100000 30%

Basically I have the formula in my head I just can't translate it into an IF especially a nested one.

the formula in my head goes a bit like this



US = Units Sold
SP = Sale Price

IF US is > 100,000 Then do SP/100*30 = Discount Price for 1 unit. If its not then do
IF US is > 50,000 But < 100,000 then do SP/100*20 and so on til I have checked all discount rates.

Is there a better/easier way of doing this?

Abarxax,

In order to get the appropriate discount rate in line with your latest posting I suggest you go with a revamp of Pecoflyer's lookup formula.

Assuming the Units Sold to be in B2 then ........
=LOOKUP(B2,{0,11000,26000,50001,100001},{0,0.05,0. 1,0.2,0.3})
will return the appropriate discount as a decimal fraction.

It's up to you as to how you need to use it. Eg whether you need to display it in a cell of it's own (Formatted as %) or whether you just need to use it as part of another formula, directly, in order to compute the discount value and /or the discounted selling price.

Eg if the Unit Selling Price is in C2 then the discount value is.....
=C2*LOOKUP(B2,{0,11000,26000,50001,100001},{0,0.05 ,0.1,0.2,0.3})

or the Discounted Selling Price is .......

=C2*(1-LOOKUP(B2,{0,11000,26000,50001,100001},{0,0.05,0.1 ,0.2,0.3}))