Repeating multiplication (sorta)

[JimDandy]

Is there a formula that can perform the following long formula just by passing into it the values 100 and 12? I don't know all the various mathematical options in Excel but thought there might be a way to pass the value to create this same result.

=(100*1)+( 100*2)+( 100*3)+( 100*4)+( 100*5)+( 100*6)+( 100*7)+( 100*8)+( 100*9)+( 100*10)+( 100*11)+( 100*12)

The result of this long formula is 7,800.

[sandy666]

maybe: =SUM(100*{1,2,3,4,5,6,7,8,9,10,11,12})

[JimDandy]

Great...thank you. That's exactly what I needed.

[CK76]

If you want 100 & 12 as variable.
Assuming A1 contains 100, B1 contains 12.
=SUMPRODUCT(A1*ROW(INDIRECT("1:"&B1)))

[sandy666]

You are welcome
thanks for rep and mark thread solved

[JimDandy]

Thanks CK76, that works also.

Although I listed this as solved, is it possible ask if either of these solutions can be modified slightly to allow it to function in a Sumproduct sort of way for performing this calculation against a group of numbers? I am multiplying the result by a certain count, but then I have a whole row of values to perform this against. I took the formula and added a bit to complete the calculation I needed such as

Please Login or Register  to view this content.

My worksheet looks like this, and I'd like to be able to perform that summing like can be done with arrays in Sumproduct.

Please Login or Register  to view this content.

[sandy666]

First show what you need. not part but full

could be in attached excel file

maybe =SUMPRODUCT(B2*ROW(1:12)*A2/ROWS(1:12))

[CK76]

So something like... below? Assuming 12 is constant.
=SUMPRODUCT(B2*ROW($1:$12))*(A2/12)

See attached.

[sandy666]

or

for defined range (from example) : =SUMPRODUCT(B2*A2:A4*A2/COUNT(A2:A4))

ok, enough this game

[CK76]

lol yeah

[JimDandy]

Fair point, I was thinking I could figure out the second part if I could get over the first question. I could not...

Attached is what I'm trying to complete. My final worksheet uses tables and has dozens of rows, so I've boiled it down to the few I'm left to work out in the attachment. My goal is to perform all of the necessary calculations described in my last post in the colored table without having to create the individual Results table.

[sandy666]

So you want get result only? without "middle" table?
Target result Year 1: C12
=SUMPRODUCT($B4:$B6*{1,2,3,4,5,6,7,8,9,10,11,12}*C$4:C$6/12)
drag to the right

[JimDandy]

Sweet....Thank you again!

[sandy666]

Sweet only? This is VERY sweet

You are welcome