Search code examples
excelrecursionexcel-formula

How to get the recursive difference formula between two columns in excel

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

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/ Another calculation I need is to recursively get the product from the difference of the columns from two rows, and it is recursive in 3 rows. This means, (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.

Solution

  • Assuming there is no Excel Constraints(MS365 Exclusive) as per the tags posted, then one could use one of the followings to accomplish the desired output:

    enter image description here

    • Formula used in cell D2

    =TOCOL(
    WRAPCOLS(
        A2:A24,
        ROWS(B2:B5)
    ) - B2:B5,
    2,
    1
)

    • Formula used in cell E2

    =D2#*VSTACK(1,DROP(D2#,-1))

    Or, To return the whole output dynamically using one single formula:

    enter image description here

    =LET(
     _xt, DROP(TOCOL(A:A,1),1),
     _xbar, DROP(TOCOL(B:B,1),1),
     _sub, TOCOL(WRAPCOLS(_xt,ROWS(_xbar))-_xbar,2,1),
     HSTACK(_sub, _sub*VSTACK(1,DROP(_sub,-1))))