Total sum based on different costs for each new quantity group
 

How do I calculate this situation?
Assumptions:
For the first 500 units, the cost per unit is $25
Between 501-1000 units, the cost per unit is $17.50
Between 1001-2000 units, the cost per unit is $10

To calculate total cost for 1,900 units, I have to say:
(500*$25) + (499*$17.50) + (901*$10)

--
t

Total sum based on different costs for each new quantity group
 

To calculate total cost for 1,900 units, I have to say:
(500*$25) + (499*$17.50) + (901*$10)


First up this bit isn't right, it should read
(500*$25) + (500*$17.50) + (900*$10)

With the following input

A1 = 25
A2 = 17.50
A3 = 10
B1 = 1900

Then put this in C1

=IF(B11000,((B1-1000)*A3)+(A2*500)+(A1*500),IF(AND(B1<1000,B1500) ,(B1-500)*A2+(A1*500),B1*A1))

HTH
Martin

Total sum based on different costs for each new quantity group
 

...........A.........B..........C.............D
1.....1900........0..........25..........25
2.................500..........17.5.....-7.5
3...............1000..........10........-7.5

=SUMPRODUCT(--(A1B1:B3), (A1-B1:B3),D1:D3)

Or, without the table:

=SUMPRODUCT(--(A1{0;500;1000}), (A1-{0;500;1000}),{25;-7.5;-7.5})

http://mcgimpsey.com/excel/variablerate.html

Biff

"MartinW" wrote in message
...
To calculate total cost for 1,900 units, I have to say:
(500*$25) + (499*$17.50) + (901*$10)


First up this bit isn't right, it should read
(500*$25) + (500*$17.50) + (900*$10)

With the following input

A1 = 25
A2 = 17.50
A3 = 10
B1 = 1900

Then put this in C1

=IF(B11000,((B1-1000)*A3)+(A2*500)+(A1*500),IF(AND(B1<1000,B1500) ,(B1-500)*A2+(A1*500),B1*A1))

HTH
Martin

Total sum based on different costs for each new quantity group
 

Thanks!
--
t


"MartinW" wrote:

To calculate total cost for 1,900 units, I have to say:
(500*$25) + (499*$17.50) + (901*$10)


First up this bit isn't right, it should read
(500*$25) + (500*$17.50) + (900*$10)

With the following input

A1 = 25
A2 = 17.50
A3 = 10
B1 = 1900

Then put this in C1

=IF(B11000,((B1-1000)*A3)+(A2*500)+(A1*500),IF(AND(B1<1000,B1500) ,(B1-500)*A2+(A1*500),B1*A1))

HTH
Martin