• 411K Members
• 5,648 Online
• 467K Conversations
SOLVED

New Contributor

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 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!

5 Replies

Re: Pricing sheet using ranges of quantity discounts

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)  )```

Re: Pricing sheet using ranges of quantity discounts

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

Solution

Re: Pricing sheet using ranges of quantity discounts

```=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

Highlighted

Re: Pricing sheet using ranges of quantity discounts

Great, thank you for the help!

Re: Pricing sheet using ranges of quantity discounts

@bagudd , you are welcome

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies