Need a formula for Calculation based on different rage

[sudi468]

I Have a calculation of product with different range.

Example:

0-100 Price will be 85
101-500 Price will be 70
501-1000 price will be 66
1001 - 2000 price will be 50
More than 2001 - 45

In this calculation, if i need 600 Product, then first 100 product will be Rs 85, for 400 product will be Rs. 70 and last 100 will be Rs. 66

can some one help me to put formula for the same, if its great share with excel file.

thanks in advance

regards,
Sudhir

[Shareez Saleem]

Can you please review the attached, are you looking for something like this.
What I did here is split the quantity in different slabs and multiplied each with the respected cost.

[mehmetcik]

=if(a1<100,a1,100)*85+if(a1<=500,max(a1-100,0),400)*70+if(a1<=1000,max(a1-500,0),500)*66+if(a1<=2000,max(a1-1000,0),1000)*50+max(a1-2000,0)*45+max(a1-2000,0)*45