Forum Discussion
Solved
Pricing sheet using ranges of quantity discounts
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 S...
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
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) )
Hi SergeiBaklan ,
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