Batch Macro help! Complex scenarios with condition and multiple formula

Question

-- Please see attached file for context --

I currently have a problem where when a condition is met (Condition = 'TRUE') I need to recalculate an amount (New Amount), based on Average * Factor. I have built a batch macro that can iterate through each row and produce column H, however this is not the desired outcome.

I am trying to produce a batch macro that produces column P, where the Cumulative field is summed using recalculated 'New Amount' values. Further the Cumulative field will only use the value in 'New Amount' if Condition = TRUE and a New Amount has actually been calculated. I have not been able to incorporate this extra logic into my macro.

Any help or suggestions is appreciated, my Macro has not been built using the Example data, but I can rebuild it if a base workflow was needed.

Example.xlsx

lmorrell · Accepted Answer

Hi @TAYoung

Solution is attached as a yxzp.

I'm a lot more familiar with iterative macros - so I decided to take that approach.

The workflow comes in two pieces, firstly the pre-processing to set up the first two rows

Followed by the iterative macro that does the step-by-step calculation of cumulatives, averages, and New Amounts

Since I was using text inputs, the data types may differ from your main source - but once the data types are consistent then the workflow should run.

This was quite a fun challenge, so please let me know if this helps