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

bagudd's avatar
bagudd
Copper Contributor
Jun 06, 2019

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

 
 
 
 
 
 
 
 
 
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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

  • bagudd's avatar
    bagudd
    Copper Contributor
    Jun 07, 2019
    Great, thank you for the help!
      • 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

Resources