Forum Discussion

bagudd's avatar
bagudd
Copper Contributor
Jun 06, 2019
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 06, 2019

    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

benuh's avatar
benuh
Copper Contributor
Mar 25, 2023

SergeiBaklan 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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 26, 2023

    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)

     

Resources