Discount based on Qty Range

[seanyeap]

Hi Members,
I have copied useful formula as below as it searches a value based on a Min Qty.

Please Login or Register  to view this content.

However, I humbly seek help to rectify the formula if the price is discounted based on a range of qty. For instance, any quantities range from 1 to 10 will be entitled for a 10% discount, whereas quantities range from 11 to 50 will be entitled for a 15% discount.

I have attached a file for better understanding, Any help is greatly appreciated. Thank you.

[dilipandey]

Hi seanyeap

See the cell L2 in green in the attached workbook..

discount based on range of qty.xls

Regards,
DILIPandey
<click on below * if this helps>

[seanyeap]

Hi Sir,
Thanks for your help and it really works. How do I click on below *?

[dilipandey]

you are welcome and * is there in bottom left side of every post


Regards,
DILIPandey
<click on below * if this helps>

[seanyeap]

Hi DILIPandey,
First of all, thanks for the wonderful formula given as below: -

Please Login or Register  to view this content.

This is obviously for quantities range from 1 to 10 (<=10 ) will be entitled for a 10% discount, whereas quantities range from 11 to 50 will be entitled for a 15% discount.
How do I add other quantities and various ranges?
Like 1-10 is 10%
11-50 is 15%
51-100 is 20%
101-500 is 25%
Qty above than 500 is 30% discount? Thanks in advance.

[seanyeap]

Hi,
Can anyone look at my problem? Thanks in advance.

[dilipandey]

Hi Seanyeap,

Try using below formula:-

Please Login or Register  to view this content.

See attachment:- discount based on range of qty.xls

Regards,
DILIPandey
<click on below * if this helps>

[seanyeap]

Hi DILIPandey,
It works like a charm, however the formula is really complex. I cannot understand. Thank you.

[dilipandey]

Hi seanyeap,

I hope vlookup function is already known by you. Apart from that its Lookup function which is multiplying required percentage, so suggest you to look into Excel inbuilt help for Lookup function. Later you can replace vlookup function with Lookup function
Thanks.



Regards,
DILIPandey
<click on below * if this helps>

[seanyeap]

Thanks a lot.

[dilipandey]

you are welcome seanyeap

Cheers

Regards,
DILIPandey
<click on below * if this helps>

[seanyeap]

HIi dilipandey,
I have managed to modify the formula to set specified discount on various qty as below:-

Please Login or Register  to view this content.

The final discounted price is shown in cell R2

Please Login or Register  to view this content.

Any value entered in A2 will reflect the price per unit after discount based on qty referring to the part number in A1 Thanks for the great formula.

[dilipandey]

Great



Regards,
DILIPandey
<click on below * if this helps>