Calculating value for rows above

[Wojtek_Dz]

Hi, 

could anybody help me with case as below:

A	B	C	D
10	20	20	=B1*C1
12	22	=C1-D1	=B2*C2
14	24	=C2-D2	=B3*C4
16	26	=C3-D3
18	28
20	30
22	32
24	34
26	36

 I wanted to calculate the value for column C and D. The value in C1 is static rest of them need to be calculated.

Any ideas ?

Thanks !

[FinnCharlton]

Hi @Wojtek_Dz , are the formulas correct? The formula for C2,C3,C4 all reference themselves in the calculation.

[gawa]

hi @Wojtek_Dz 

Multi row formula is the best. Multi row formula can refer to another records for calculation, which Excel users are already familiar with. In your case, you want to have a static value at first row(C2), then you have to put a conditional statement like below(Do not forget "Values for Rows that don't exist" to NULL, otherwise it will not work properly in this case)

By the way, though I configured your calculation method, and results are going to diverge, which exceeds the limit of INT64 at last row. Could you check your calculation method is surely correct??

[Wojtek_Dz]

@FinnCharlton  corrected :)

[Wojtek_Dz]

thanks @gawa 

Yes, I also tried to solve it by multi-row tool but I failed.

In your solution in my opinion there is still missing something.

If we take the row 2 you can noticed that we need for in the calculation from row 1 for C and D.

So iI think that we can't calculate the col C as 1st step, and then calculate col D  :(

[FinnCharlton]

Hi @Wojtek_Dz , here you go. As the columns are dependent on each other being filled at the same time, we need to transpose the data before we calculate the multi-row formula. These numbers get massive, would be interested to know what you're trying to compute here.

[Wojtek_Dz]

thanks @FinnCharlton 

but as I replied to @gawa  solution if we want to calculate C2 we have to subtract C1 from D2 (which has to be calculated earlier) :(

[FinnCharlton · Accepted solution]

@Wojtek_Dz , my solution does exactly what the formulas in your table say. To calculate C2, we do C1-D1, D2 is not involved:

If this isn't correct please clarify your formulas.

[Wojtek_Dz]

Thanks @FinnCharlton , 

You are right, your solution is cool. 

I was wrong :)

Thank you very much for your help !