Forum Discussion

MichielS115's avatar
MichielS115
Copper Contributor
Jul 13, 2024

Excel iterate over table and list output with spilling formula

Hi, i am looking for a formula that iterates byrow and presents the differences between the months like the output below. I am not fully sure how to tackle the problem with a spilling formula. Somebody any suggestions how to approach (especially the reference to the previous value and the way you should iterate the table (with map or a combination byrow / bycol). 

thanks in advance,

 

Michiel 

  • djclements's avatar
    djclements
    Bronze Contributor

    MichielS115 An alternative lambda-free method (non-iterative) could be:

     

    =LET(
        table; A1:O7;
        col_labels; DROP(TAKE(table; 1);; 1);
        row_labels; TAKE(DROP(table; 1);; 1);
        values;     DROP(table; 1; 1);
        net_change; values - HSTACK(TAKE(values;; 1); DROP(values;; -1));
        VSTACK(
            HSTACK("Item"; "Date"; "Net Change");
            HSTACK(
                TOCOL(IF(SEQUENCE(; COLUMNS(values)); row_labels));
                TOCOL(IF(SEQUENCE(ROWS(values)); col_labels));
                TOCOL(net_change)
            )
        )
    )

     

    Adjust the table range reference as needed.

     

    Also, if you prefer blanks instead of zeros for the first month, adjust the net_change variable as follows:

     

    =LET(
        ...
        net_change; IFNA(HSTACK(""; DROP(values;; 1) - DROP(values;; -1)); "");
        ...
    )

     

    See attached...

    • MichielS115's avatar
      MichielS115
      Copper Contributor
      Nice solution with good outcomes. thans a lot
      I need a bit of time to fully understand the logic behind it 😅

Resources