Hi, could anybody help me with case as below: ABCD102020=B1*C11222=C1-D1=B2*C21424=C2-D2=B3*C41626=C3-D3 1828 2030 2232 2434 2636 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 !

Calculating value for rows above

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 !

Datasets

Accepted answers

FinnCharlton

@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.

All comments

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??

1186973_gawa.yxmd

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.