Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Mar 11 '13 at 11:40

This question came from our site for professional and enthusiast programmers.

    
Do you have any part of the formula working in Excel? –  James Jenkins Mar 10 '13 at 20:28
    
If you check back, try posting the Excel Formula, as it is you are asking some to a broken LibreOffice so it will work in Excel, without any idea about what you are trying to do. stackoverflow.com/faq –  James Jenkins Mar 10 '13 at 21:03
    
Yes, I do. The first 8 columns have been resolved but "hard-coded" (if that makes sense in spreadsheets). At the present time, I can provide you the formula but not the Excel formula, as this is my requirement. This is the formula : ∑_n^2▒〖H_2*I_n+J_2*K_n+H_3*I_(n-1)+J_3*〗 K_(n-1)+⋯+H_(n-1)*I_3+J_(n-1)*K_3+H_nI_2+J_nK_2. Do you still want it in another format? –  thitami Mar 10 '13 at 21:10
    
This could be better: Sum[H2 *In + J2 *Kn + H3 *In-1 + J3 *Kn-1 +……+ Hn-1 *I3 + Jn-1 *K3 + Hn *I2 + Jn *K2 ] N=163 –  thitami Mar 10 '13 at 21:18
    
Sorry I can't help you with that, this does look like anything I have seen in Excel –  James Jenkins Mar 10 '13 at 21:30

1 Answer 1

up vote 4 down vote accepted

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

Image of suggested approach

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...):

Picture illustrating final answer

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.

share|improve this answer
    
I think that you have got the point, Reinier. As a guide, I am attaching you a relevant xls link , to make it easier for me to explain it. Is it feasible to resolve it without the helper column ? –  thitami Mar 10 '13 at 23:07
    
Thanks for the XLS, that made your question clear. I updated the answer and provided a response-XLS as well. –  Reinier Torenbeek Mar 12 '13 at 3:35
    
+1 for not trying to do it all in one cell. Too many people don't realise you have all these blank cells to make your life easier. –  Hand-E-Food Mar 12 '13 at 5:29
    
That was a very helpful answer, Reinies. Much appreciated. –  thitami Mar 13 '13 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.