Iterative Macros - multiple columns with dependency

Question

Dear all,

I tried to use iterative macro to loop for 365 days as the dependency between each field would cause difference answer after I reapply the formula.

e.g.

DATE UNIT_POSITIVE  UNIT_NEGATIVE  MKT PRICE  Unit c/f  Avg price  Amount c/f 12/15/2025                 -                   -          22.00                 -                   -                           -  12/16/2025        60.00                 -          23.00        60.00   23.0000         1,380.0012/17/2025        50.00      (25.00)        23.00        85.00   23.0000         1,955.0012/18/2025                 -                   -          22.00        85.00   23.0000         1,955.0012/19/2025                 -                   -          22.00        85.00   23.0000         1,955.0012/20/2025           5.00         (8.00)        22.00        82.00   22.9390         1,881.00

but Avg price and Amount c/f  are newly created from the original data set

Can anyone help to create a new iterative macro to cater such issue? I try to add [Engine.IterationNumber] into formula and + 365 for loop but in vain. Thanks!

Sample Data.xlsx

kristopherlam · Answer

Given the data of avg price rely on amount c/f. While the amount c/f rely on the row-1 avg prive if there is any sales transaction. if I only use 1 set of generate rows, multi row formula and a formula like amount c/f / avg price only the avg price would be incorrect. as the avg price adopted before is the old one. and the transaction would apply to 365 days

TheMattLeonard · Answer

Can you help me understand what the starting point of your data is and what you are iterating? Seems to me that what you might need is a combination of Generate Rows, Sort, and Multi Row Formula