Circular Reference/Iterative Problem

[InOverMyH3ad]

I am killing myself trying to solve a problem of pennies!


Source 1 is a pricing sheet that has a total at the bottom. We frequently use goal seek to adjust margin to get a desired number.

Accounting 2 is a sheet for accounting that takes numbers from Source 1 and regroups them. The numbers from Accounting 2 should match the total to Source 1.

There is a simple check on sheet two that says Source 1 total price - Accounting 2 total price and has conditional formatting if it doesn't =0

Due to rounding on Accounting 2, I'm running in to scenarios where I can be .01 off. I would like to take whatever the resulting difference is in my check and add it to one of my groups on the Account 2 sheet to make the difference 0. The problem is this creates a circular reference. I started looking in to iterative functionality but the problem is when I perform goal seek on the source sheet, the difference is stored every iteration goal seek does until it reaches the goal number; I just need it to be done at the very end.

Is this possible?

 

[Jeffrey Mahoney]

If you're willing to accept the .01 difference, why not test for that. IF(ABS(A1-A2)<.02,"OK","Not OK")

My experience with rounding is problematic. I tend to use number formatting to display the rounding during the process. It isn't until I get the final result do I actually round the value to a certain decimal place. You may want to do that so that until your balance test is complete and then round the value.

Jeff