Total sum based on different costs for each new quantity group

[Guest]

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)

 

[MartinW]

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(B1>1000,((B1-1000)*A3)+(A2*500)+(A1*500),IF(AND(B1<1000,B1>500),(B1-500)*A2+(A1*500),B1*A1))

HTH
Martin

 

[T. Valko]

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

=SUMPRODUCT(--(A1>B1:B3), (A1-B1:B3),D13)

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

 

[Guest]

Thanks!
--
t

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(B1>1000,((B1-1000)*A3)+(A2*500)+(A1*500),IF(AND(B1<1000,B1>500),(B1-500)*A2+(A1*500),B1*A1))

HTH
Martin