Question

I am stuck on the recursive calculation on excel. Now I have two columns like below:

Now I need to calculate two results from these two columns: 1/ In every 4 rows, the left column recursively subtracts the right column from the 4 rows number, which means, xt1-xbar1, xt2-xbar2, xt3-xbar3, xt4-xbar4, xt5-xbar1, xt6-xbar2,... The left column will be called each row, but every 4 rows after, it will subtract the 1st row back from the left column.

2 另一种计算方法是,从两行各栏的差额中收回产品,在三行中重新计算。这意味着(xt2-xbar2)(xt1-xbar1),(xt3-xbar3)(xt2-xbar2),(xt4-xbar4)(xt3-xbar3),(xt5-xbar1)(xt4-xbar4),(xt6-xbar2)*(xt5-xbar1),......

Could anyone help me work out the formula above? Thanks.

Answer 1

Different Sized Columns Difference feat. a Running Product

http://www.ohchr.org。 (减):

=LET(xt,A2:A15,xbar,B2:B5,
    xt-CHOOSEROWS(xbar,MOD(SEQUENCE(ROWS(xt))-1,ROWS(xbar))+1))

在<代码>D2(复制):

=LET(data,C2#,data*VSTACK(1,DROP(data,-1)))

Excel 2021

=LET(xt,A2:A15,xbar,B2:B5,
    xt-INDEX(xbar,MOD(SEQUENCE(ROWS(xt))-1,ROWS(xbar))+1))

=LET(data,C2#,
   s,SEQUENCE(ROWS(data))-1,
   data*IF(s=0,1,INDEX(data,s)))

Different Sized Columns Difference feat. a Running Product

友情链接