Complex formula in Excel

Question

I am trying to apply a complex formula in an entire Excel spreadsheet column.

What makes it complex, apart from the fact that I am not experienced in that level of Excel, is the fact that the formula parts are being increased as the formula proceeds.

LibreOffice Calc "translates" it to : sum from {n} to {2} {{H} rsub {2} * {I} rsub {n} + {J} rsub {2} * {K} rsub {n} + {H} rsub {3} * {I} rsub {n-1} + {J} rsub {3} *} {K} rsub {n-1} +…+ {H} rsub {n-1} * {I} rsub {3} + {J} rsub {n-1} * {K} rsub {3} + {H} rsub {n} * {I} rsub {2} + {J} rsub {n} * {K} rsub {2}

How could I resolve it? Any suggestions?

Thanks

Answer 1

If I understand the question correctly, then you could resolve this with an approach like this:

The important formula is as follows:

=INDEX($H:$H,ROW())*(INDEX($I:$I,8-ROW()))+INDEX($J:$J,ROW())*INDEX($K:$K,8-ROW())

Enter this into cell F2 and drag the right bottom corner all the way down to the end. then G2 contains the formula

=SUM($F$2:$F$6)

Note that the number 8 in F2 is just to match the example screenshot, which has rows running from 2 to 6. If you need to run to 163, then you need to adjust the formula accordingly.

You could do this without the helper column, fold everything into a more complicated formula. However, for now I am not even sure whether this is what you are looking for...

An example of the workbook is uploaded here

---

Update:

After you provided the guiding XLS, I understood that the calculation you need is actually more complex. Copying from your uploaded XLS, what you need are the following formulas, where each next cell in the row gets more complicated:

=H2*I2+J2*K2
=H3*I2+J3*K2+H2*I3+J2*K3
=H4*I2+J4*K2+H3*I3+J3*K3+H2*I4+J2*K4

and so on.

This looks like a sort of

=SUMPRODUCT(H,I)+SUMPRODUCT(J,K)`

but with columns I and K reversed in order, and with the lengths of the column depending on the row you are calculating.

Anyway, the best I could come up with, was to introduce two helper columns N and O that contain the up-side-down versions of columns I and K, and then use the following formula in column Q:

=SUMPRODUCT(OFFSET($J$2,0,0,SUM(ROW())-1),OFFSET($O$2,11-SUM(ROW()),0,SUM(ROW())-1))
 +SUMPRODUCT(OFFSET($J$2,0,0,SUM(ROW())-1),OFFSET($O$2,11-SUM(ROW()),0,SUM(ROW())-1)

Credits go to this answer for the SUM(ROW()) hack.

N and O are filled as follows:

=INDEX($I$2:$I$11,COUNTA($I2:$I$11),1)

Corresponding picture (with a slight change in the formula, but I did not care to re-take the screenshot...):

I uploaded the corresponding workbook here. Of course, you have to edit some of the formulas to support longer columns. Or make everything completely dynamic, which is not too hard but results in long formulas.

If you do not like the view of the helper columns M and O, then you can hide them.