Iterative macro not summing correctly - please help!

[michaelc95]

Hi there,

I have created an iterative macro (attached: "iterative macro") in order to calculate payments for staff. To do this calculation, I need to sum three columns together - [KE: Actual Weekly Gross] + [AWE Allowances] + [MZ]. These three fields sum to the field [NA (AWE)]. Once [NA (AWE)] is calculated, the prior 52 values of this entry are summed to create the field [KN].

The value for [MZ] is variable, whilst [KE: Actual Weekly Gross] and [AWE Allowances] is fixed (coming straight from the raw data file - "v5.0 main - interim results". As [MZ] is variable, and [NA (AWE)] is calculated based off prior entries, this needs to be an iterative macro. Now this is where it gets interesting..

The sum for [NA (AWE)] computes fine, however when I try and do the sum function for [KN], it computes accurately at first, however eventually starts creating variances from what is expected. 

I suspect this is being by [MZ] being computed a number of times in the macro (and hence in the final output being wrong), however I am not sure.

If anyone could please help, that would be much appreciated!

I have attached the following:

• main workflow.yxmd - this is the overarching flow 
• iterative macro.yxmc - this is the iterative macro
• v5.0 main - interim results.xlsx - this is the raw data feeding the macro
• 00533283.3.xlsx - this is the output file produced.

Note, I have annotated the output excel file showing a manual comparison for what should be output and also the variance between expected and actual results.

Thank you.

[Qiu]

@michaelc95 
I think this may relate to the floading error and trying to find a way.

But can you check your input and output data, since it appears that the MZ column are different.

[michaelc95]

@Qiu can you please explain the floading error.

That is correct, they are different - in the input data MZ is zero. However throughout the iterative macro, this value gets updated to produce the output data. 

For each row of MZ, i only need it to be calculated once (the one time the row is output). At the moment I think MZ is being calculated and overwritten multiple times prior to output.

Any solutions/ideas would be much appreciated.

[Qiu]

@michaelc95 
Let me check again how the "MZ" is caculated in your workflow and get back to you. 😁