Summing the multiplication of different portions of a number by different values

[Excel n00b2]

I suspect the answer to this question is already out there but I don’t know the mathematical term for it so don’t know what to search for.

Essentially I want to be able to sum the result of multiplying different portions of a number by different values e.g. suppose one cell contains the number 77 and another contains 88. I want to find the sum of multiplying up to the first 35 by 5, then the next 25 by 4, then the next 15 by 3, then the next 10 by 2, then the remaining by 1, so the result for each cell would be 324 and 343.

If the answer is using multiple IF statements and “>” and/or “<“, then that’s how I’m already doing it.

 

[Peter_SSs]

Welcome to the MrExcel board!

Would using a lookup table like this be any use to you?

20 05 30.xlsm
	A	B	C	D	E	F	G
1	Number	Result			0	0	5
2	77	324			35	175	4
3	88	343			60	275	3
4	185	440			75	320	2
5	10	50			85	340	1
6	44	211
Mult

Cell Formulas
Range	Formula
B2:B6	B2	=VLOOKUP(A2,E$1:F$5,2)+(A2-VLOOKUP(A2,E$1:E$5,1))*VLOOKUP(A2,E$1:G$5,3)

 

[Amit Tandon]

I suspect the answer to this question is already out there but I don’t know the mathematical term for it so don’t know what to search for.

Essentially I want to be able to sum the result of multiplying different portions of a number by different values e.g. suppose one cell contains the number 77 and another contains 88. I want to find the sum of multiplying up to the first 35 by 5, then the next 25 by 4, then the next 15 by 3, then the next 10 by 2, then the remaining by 1, so the result for each cell would be 324 and 343.

If the answer is using multiple IF statements and “>” and/or “<“, then that’s how I’m already doing it.

Enter below formula in cell B2, for the cell A2 value:

=SUMPRODUCT((A2>{85,75,60,35,0})*(A2-{85,75,60,35,0})*{-1,-1,-1,-1,5})


You may copy the formula down from cell B2 for subsequent column A values.

 

[Excel n00b2]

Thanks for the welcome Peter and thank you both for your solutions. I think Amit’s is closer to the solution I’m looking for but I’m having trouble using cell references, as Peter did, instead of static values in the array, which I would like to do so I can change the value of a cell and it automatically recalculate the result without having to update the formula and fill it down again e.g. instead of “{85,75,60,35,0}” could I do something like “{E5,E4,E3,E2,E1}”? It would be even better if I could do something like “{SUM(E5:E1), SUM(E4:E1), SUM(E3:E1), SUM(E2:E1), E1}” where E5:E1 were the original values i.e. 35, 25, 15, 10, 0.

 

[Amit Tandon]

Enter below formula in cell E2 and copy down - refer below image:

=SUMPRODUCT((A2>(B$2:B$6))*(A2-(B$2:B$6))*(C$2:C$6))

Cells B2:B6 contain "85,75,60,35,0" (cumulative values) and C2:C6 contain "-1,-1,-1,-1,5" (differential values).

 

[Amit Tandon]

The logic for cells B2:B6:

B6 = 0
B5 = B6 + 35
B4 = B5+25
B3 = B4+15
B2 =B3+10

which returns "85,75,60,35,0" for B2:B6


For cells C2:C6:

C6 = 5 (start value)
C5 =4-SUM(C6:C$6)
C4 =3-SUM(C5:C$6)
C3 =2-SUM(C4:C$6)
C2 =1-SUM(C3:C$6)

Which returns "-1,-1,-1,-1,5" for C2:C6