Iteration and macros

[Ken G.]

I have a sheet which calculates a loan amount which also includes a fee which
is a percentage of the loan amount, so I have iteration active to avoid the
circular reference error. To cover people who don't have iteration on there
is a macro which automatically turns it on when the sheet is opened.

The problem is that the formula gives diffrent results on different PC's. We
are all running the same version of excel (2003), but some have macro
security set to low, others to medium. Mine was set to medium and the result
differed from someone who had theirs set to low. I set mine to low and the
result changed to agree with the other PC (also set to low). When I reset
mine to medium the result did not change back, and now regardless of the
security setting gives the same result which is different from the what it
was originally showing. (I hope this makes sense!)

The difference is only one dollar, but for loan documentation it has to be
the same across the board. Can anyone suggest why this is happening? The only
other macro in the sheet is one that resets the input cells to their default
values. It doesn't modify any formulas.

 

[Niek Otten]

Impossible to say without your formulas, inputs, settings and results!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have a sheet which calculates a loan amount which also includes a fee which
| is a percentage of the loan amount, so I have iteration active to avoid the
| circular reference error. To cover people who don't have iteration on there
| is a macro which automatically turns it on when the sheet is opened.
|
| The problem is that the formula gives diffrent results on different PC's. We
| are all running the same version of excel (2003), but some have macro
| security set to low, others to medium. Mine was set to medium and the result
| differed from someone who had theirs set to low. I set mine to low and the
| result changed to agree with the other PC (also set to low). When I reset
| mine to medium the result did not change back, and now regardless of the
| security setting gives the same result which is different from the what it
| was originally showing. (I hope this makes sense!)
|
| The difference is only one dollar, but for loan documentation it has to be
| the same across the board. Can anyone suggest why this is happening? The only
| other macro in the sheet is one that resets the input cells to their default
| values. It doesn't modify any formulas.
|

 

[Ken G.]

Unfortunately I can't do that, so I'll just have to sort this out myself. I
was hoping someone may have had a similar experience. I can change the final
result simply by selecting a cell that goes into the iteration formula and
without changing anything click the green "tick" and the result will change.
In the example causing the problem one figure ends up with 99 cents on the
end and it seems that the iteration will sometimes tip this over to the next
dollar and sometimes not.

Is there any way of seeing how many iterations are performed before it
arrives at the final value?

 

[Roger Govier]

Hi Ken

I don't think Niek was suggesting you posted any sensitive data, just an
example of the formula you are using and the type of inputs and the
result you are trying to achieve.

It may be that someone can suggest a formula that will not rely upon
iteration to achieve the desired result, and thereby eliminate the
problem.