Problem with price per item calculator that needs to apply volumebased prices that stack

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I'm trying to build an excel sheet that

asks for the amount of lines you have A1

then takes that number of lines and applies one of 3 prices per line,
assigning a volume discount based on the number of lines you have

the rules are

for the first 10 lines, you pay $22 a line (B1)
for lines 11 - 20, you pay $18 a line (B2)
for line 20+ (up to 100) you pay $15 a line (B3)

but the prices need to stack

IE

10 lines: (10*22) - Total cost of $220 per month
11 lines: (10*22)+(1*18) - Total cost of $238 per month
20 lines: (10*22)+(10*18) - Total cost of $400 per month
21 lines: (10*22)+(10*18)+(1*15) - Total cost of $415 per month

I started with this formula

=IF(A1<11,A1*A2,IF(A1>10<21,A1*C2,A1*B2))

But then realised

A: I had a problem with the middle IF(A1>10<21... less than greater
than problem
B: That this formula was not going to be stacking the prices

Any help guys woould be much appreicated
 
I'm trying to build an excel sheet that

asks for the amount of lines you have A1

then takes that number of lines and applies one of 3 prices per line,
assigning a volume discount based on the number of lines you have

the rules are

for the first 10 lines, you pay $22 a line (B1)
for lines 11 - 20, you pay $18 a line (B2)
for line 20+ (up to 100) you pay $15 a line (B3)

but the prices need to stack

IE

10 lines: (10*22) - Total cost of $220 per month
11 lines: (10*22)+(1*18) - Total cost of $238 per month
20 lines: (10*22)+(10*18) - Total cost of $400 per month
21 lines: (10*22)+(10*18)+(1*15) - Total cost of $415 per month

I started with this formula

=IF(A1<11,A1*A2,IF(A1>10<21,A1*C2,A1*B2))

But then realised

A: I had a problem with the middle IF(A1>10<21... less than greater
than problem
B: That this formula was not going to be stacking the prices

Any help guys woould be much appreicated
Click to expand...

This approach is easily expandable.

Set up a Rate Table (Name it RateTbl) as follows:

Qty Base PerLine
0 0 22
10 220 18
20 400 15


Base is the price for the number of units in Qty. It can be computed using the
formula:

Assume the table is in I1:K4

J3: =(I3-I2)*K2+J2
and fill down as far as needed.

Then you can use this formula:

=VLOOKUP(A1,RateTbl,2)+(A1-VLOOKUP(A1,RateTbl,1))*VLOOKUP(A1,RateTbl,3)

If you need to make changes, including not only different pricing, but
different or more break points, you can easily change the table without any
need to change any of the dependent formulas.
--ron
 
Same solution as the others, just different.

=MIN(22*A1, 100+15*A1, 40+18*A1)

- -
Dana DeLouis
 
Back
Top