Pricing sheet using ranges of quantity discounts

[bagudd]

I'm building a pricing sheet and need the below table to show the cumulative pricing based on the ranges.  Anybody any idea to do this without a bunch of nested IF's?

Range Start	Range End	Standard discount	List Price	Discounted price
0	49	0,00%	€ 30,00	€ 30,000
50	99	5,00%	€ 30,00	€ 28,500
100	499	10,00%	€ 30,00	€ 27,000
500	2.499	15,00%	€ 30,00	€ 25,500
2.500	4.999	20,00%	€ 30,00	€ 24,000
5.000	9.999	25,00%	€ 30,00	€ 22,500
10.000	14.999	30,00%	€ 30,00	€ 21,000
15.000	19.999	35,00%	€ 30,00	€ 19,500
20.000	100.000	40,00%	€ 30,00	€ 18,000

	Quantity	Total	Unit
Example1:	75	€ 2.211,000	€ 29,480
Example2:	2550	€ 65.919,000	€ 25,851

The first example is the first 49 licenses @30€, the next 75 - 49 = 26 @ 28,5€

The second example uses same logic, within each range the number of license at their rate + last 50 @24€

I'm looking for a magic formula in the Total cell in bold to perform the calculation based on the quantity cell.

Thanks!

[#2]

@bagudd ,

If change the start of the first range on 1

when the formula could be

=SUMPRODUCT((  ($C$3:$C$11-$B$3:$B$11+1)*($B14>$C$3:$C$11)+
               ($B14-$B$3:$B$11+1)*($B14<=$C$3:$C$11)*($B14>$B$3:$B$11))*
             $E$3:$E$11*(1-$D$3:$D$11)  )

[#3]

Hi @Sergei Baklan ,

Big thanks for the feedback. 

I do see an error when entering the tilting values, like 50, that shows the same value as for 49.  Same goes for 100, 500 etc.

Any idea what the fix could be?

Thanks

[#4 · Accepted solution]

@bagudd , please check this

=SUMPRODUCT((  ($C$3:$C$11-$B$3:$B$11+1)*(B14>$C$3:$C$11)+
                (B14-$B$3:$B$11+1)*(B14<=$C$3:$C$11)*(B14>=$B$3:$B$11))*
             $E$3:$E$11*(1-$D$3:$D$11)  )

"more" is changed on "more or equal to", in red above

[#5]

Great, thank you for the help!

[#6]

@bagudd , you are welcome

[#7]

@Sergei Baklan hi there - this is a great solution. Wondering how you can do this to assign costs to two separate organizations within a company that use the same pricing? For example

In year 1 organization X has a quantity of 48 and organization Y has quantity of 40. The total quantity is gets you to 88 for the next discount level. Org X gets charge 48 units with discount applied and org Y gets charged 40 units.

thanks

[#8]

@benuh 

I guess total discount for the company you may calculate based on above formula for total units sold (88) by all organizations of the company (X and Y).

How to distribute discount depends on company management, it could be proportionally to units sold, i.e.

Org X total discount = 48/(40+48)*(total company discount)