Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format group of rows based on condition | Excel Discussion (Misc queries) | |||
calculate price * quantity = total amount in a row of excel forml. | Excel Worksheet Functions | |||
Subtotaling based on more than one group | Excel Worksheet Functions | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |