1

Cell B3 equals a number of Item 1 purchased with a variable pricing structure. I have a formula that works great and creates an accurate $ cost

=IF(B3<5,7188*B3,IF(B3<=19,(B3-4)*4788+28752,IF(B3>19,(B3-19)*3588+100572)))

Items #1 purchased: 1-4=$599 per mth, 5-19=$399 per mth, 20+=$299 per mth

This allows me to have a variable total cost based upon the number purchased.

Cell B4 is the second item with the following price structure: 1-4=$524 per mth, 5-19=$349 per mth, 20+=$249 per mth

My problem is that if they purchase some of the second B4 the cost is variable like above, but you start using the second values after the first purchased items reach the 1-4,5-19,20+ threshold

Second formula for the second item purchased is currently (but not working): 

=IF(B4<5,6288*B4,IF(B4<=19,(B4-4)*4188+25152,IF(B4>19,(B4-19)*2988+87972)))

If I sell 6 of the first, it is 4*$599*12mth + 2*$399*12mth and 2 of the second item, we have passed the 4 threshold, so the second item would now be sold at B4*349*12mths

I cannot figure out a formula that will work.

Improve this question
6
  • How does $599 per mth reflect in the formula? How do the numbers 7188 and 4788+28752 and 3588+100572 relate to $599 and $399 and $299?
    – teylyn
    Jan 11, 2016 at 8:34
  • @teylyn Divide them by 12. Apparently mth = month and he is returning a yearly price.
    – Ron Rosenfeld
    Jan 11, 2016 at 12:30
  • That is correct, but you can forget those 4788+28752 & 3588+100572 numbers. I basically need to understand a way to do the following. B3 is the number of item #1 purchased B4 is the number of item #2 purchased
    – Ed Regis
    Jan 11, 2016 at 23:51
  • Think of this as a subscription with a economies of scale pricing. B3 is the # of item 1 subscriptions There is a monthly maint charge based upon # subscribers 1-4=$599 per month, 5-19=$399 per month, 20+=$299 per month B4 is the # of item 2 subscriptions 1-4=$524 per month, 5-19=$349 per month, 20+=$249 per month The trick of the question is that if they buy 7 item #1s and 25 item #2s 4*$599*12mth + 3*$399*12mth then + 12*$349*12mth + 13*$249*12mth = $102,216 =IF(B4<5,6288*B4,IF(B4<=19,(B4-4)*4188+25152,IF(B4>19,(B4-19)*2988+87972))) I cannot figure the switch from item1 to 2
    – Ed Regis
    Jan 12, 2016 at 0:16
  • 4
    Possible duplicate of EXCEL Formula economies of scale calculation. The question was resubmitted with slightly clearer wording and answered.
    – fixer1234
    Jan 17, 2016 at 17:34

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .