CODEWITHSUNDEEP
excelexcel-formula
JLN
JLN

Reputation: 1

Repeat a Calculation for n times

I have a Formula that returns a value x and I want to multiply this with another certain value y and I want to repeat this n Times and RETURN the SUM of all those multiplications. I have looked into OFFSET but couldnt quite figure it out and maybe thats not even the solution for this.

enter image description here

Upvotes: 0

Views: 1968

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

Use INDEX to set the end of the range and SUMPRODUCT with LEFT to get the correct number:

=SUMPRODUCT(LEFT(D2:INDEX(D2:I2,B1),FIND("@",D2:INDEX(D2:I2,B1))-1)*D1:INDEX(D1:I1,B1))

enter image description here

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23283

You can do this with SUMPRODUCT() and INDIRECT().

Assuming you can get the data left of the @, and transpose the data, use:

=SUMPRODUCT(INDIRECT("$A$1:$A$"&ROW(A1)+$A$8-1),INDIRECT("$B$1:$B$"&ROW(B1)+$A$8-1))

enter image description here

Upvotes: 0

Related Questions