06-06-2019 06:43 AM
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!
06-06-2019 11:18 AM - edited 06-06-2019 11:19 AM
@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) )
06-06-2019 12:01 PM
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
06-06-2019 01:37 PM
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
06-06-2019 11:57 PM