Batch Macro help! Complex scenarios with condition and multiple formula

[TAYoung]

-- 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.

[lmorrell · Accepted solution]

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

[TAYoung]

Thank you so much... it looks like you've done it. I knew it would be complex but didn't think it would get that complex. 

It will take me some time to go through your workings and understand it in detail, then apply it to my real dataset. I will give you an update in a few days when I have got that far.

Thank you again for your help.