formula for discounts

[Abarxax]

Hi All,

I am having real trouble with a forumla. basically I need it to check a range of different discount rates.



in english it goes

if units sold is between 1-1000 give no discount, however if it is between 1000 & 5000 apply this discount and so on for about 4 differnet percentage rate. is there an easier way of doing this than if statements as I am struggling to get the logic of it right.

[Snakehips]

Abarxax,

Structure it as below example starting with the low volume discount and then progress through the larger discounts.
Eg 1 -1000 0%, 1001 - 5000 5%, 5001 - 10000, 10%, 10000 + 15%

Assuming Qty in B2 and Unit Price in C2 then
=B2*C2*(1-IF(B2<1001,0%,IF(B2<5001,5%,IF(B2<10001,10%,15%))) )

Alternatively you could use a lookup table approach.

Hope that helps.

[Abarxax]

Originally Posted by Snakehips

Abarxax,

Structure it as below example starting with the low volume discount and then progress through the larger discounts.
Eg 1 -1000 0%, 1001 - 5000 5%, 5001 - 10000, 10%, 10000 + 15%

Assuming Qty in B2 and Unit Price in C2 then
=B2*C2*(1-IF(B2<1001,0%,IF(B2<5001,5%,IF(B2<10001,10%,15%))) )

Alternatively you could use a lookup table approach.

Hope that helps.

That does a lot however is there a way to get it to do this for between two numbers like if it is between say 1000 & 5000 give 0%, if it is greater than 5000 but less that 10000 give 5% discount and so on?

[Snakehips]

Because the IF statements are evaluated left to right, using .... <each top limit +1 , will effectively do just that will it not?

My formula is an example with 4 discounts but you can reduce it to two ranges if you wish.

If it is important to exclude quantities less than say 1000 receiving 0% discount or 1000000 receiving the top discount ??? then you could add some > conditions.

[Pecoflyer]

Another formulation

=B2*C2*LOOKUP(B2,{0,1001,5001,10001},{0,0.05,0.1,0.15})

You can replace the arrays with ranges making the formula more flexible

[Abarxax]

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?

[Snakehips]

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}))