Spreadsheet question

By Lisa201 (Trident Captain) Date 2013-05-01 20:16

I have a long formula that i'm trying to find a faster way to write. Here's the long version, I'm wondering if I can use a function or something to do the same thing instead of this huge line...

=D2*$B2+D3*$B3+D4*$B4+D5*$B5+D6*$B6+D7*$B7+D8*$B8+D9*$B9+D10*$B10+D11*$B11+D12*$B12+D13*$B13+D14*$B14+D15*$B15+D16*$B16+D17*$B17+D18*$B18+D19*$B19+D20*$B20+D21*$B21+D22*$B22+D23*$B23+D24*$B24+D25*$B25+D26*$B26

I would think there would be some way to do something like: for i = 2 to 26; total = total + Di*$Bi; done;

Sometimes I hate spreadsheets....

By Bladerus Mortus (Councilor / Trident Captain) Date 2013-05-03 11:06 Edited 2013-05-03 11:09

the easier way is to divide and conquer...

Make a separate column with only the multiplication terms. then you can freely apply a simple SUM function to summarize the column. You can hide the extra calculation column, if you don't want it to be visible.

the more complicated way, write a visual basic macro, that uses the for next cycle and do that calculation in if you put the formula into a function you can even call the function from a cell.

I hope that helps :)

- - - Edit added text from here - - -

3rd option:

1st cell: a*b
2nd cell below: c*d+the cell above
drag and copy 2nd cell until the last relevant line. the final result will be in the last cell

By Lisa201 (Trident Captain) Date 2013-05-03 14:22

Good idea's! This is for a table of stage setups, so I have up to 10 sections like this... But I think your second solution will work! Thanks!