display of calculation result is wrong

[Dan]

I've created a spreadsheet where certain input values
seem to cause an incorrect value to appear in a cell.
The calculation result should display the calue of 90%,
but the cell shows a value of 168%. I have two other
cells that reference this cell: one shows the corerct
value of 90%, and the other shows 168%. This is really
weird - I've neve seen this before. Why is Excel showing
the wrong value?

I checking the calculation with my hand calculator, the
correct value is 90%. When I cut and paste "values" to
another cell, the number is the incorrect value of 168.
While the workbook does not report any errors or
unresolved circular references, this display error occurs
when a pick inputs that cause the maximum interations to
occur when the workbook calculates. I've not been able
to eliminate this problem with the specific input values
for which the error appears even though I've tried
various iteration combos of precision and number of
iterations.

Anybody have a clue on this? thanks!

 

[Niek Otten]

Formulas? Values? Expected results? Obtained results?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Kevin Stecyk]

I've created a spreadsheet where certain input values
seem to cause an incorrect value to appear in a cell.
The calculation result should display the calue of 90%,
but the cell shows a value of 168%. I have two other
cells that reference this cell: one shows the corerct
value of 90%, and the other shows 168%. This is really
weird - I've neve seen this before. Why is Excel showing
the wrong value?

I checking the calculation with my hand calculator, the
correct value is 90%. When I cut and paste "values" to
another cell, the number is the incorrect value of 168.
While the workbook does not report any errors or
unresolved circular references, this display error occurs
when a pick inputs that cause the maximum interations to
occur when the workbook calculates. I've not been able
to eliminate this problem with the specific input values
for which the error appears even though I've tried
various iteration combos of precision and number of
iterations.

Anybody have a clue on this? thanks!

Can you share with us the formula and the input values? Otherwise it is
likely impossible for us to help you.

One thing you can try doing is highlighting portions of the formula is the
formula bar, and pressing F9 to evaluate. You might see something
unexpected that explains why you are not getting the anticipated answer. Be
sure to press esc otherwise the evaluated portion "sticks".

Regards,
Kevin

 

[dan]

The specific formula is:

=IF(AK4="Phase 1",IF(AK46=0,IF(AJ46<0,(AJ13+$E$134+ROUNDUP
(-AJ46/
(Payback_Percentage*Phase_I_Price*PI_Ab_Rate),0))/Phase_I_
Lots,0),IF(AL5="Phase 2",IF(AK5=" ",
(AK13+$E$134)/Phase_I_Lots,0),0)),0)

The expected result is 91% and is correctly displayed in
one cell that references the cell with the formula, but
is incorreclt shown as 170% in the original cell and in
another cell that also references this result.

The values of the formula inputs are:

AK4 = Phase 1
AK46 = -$50,036
AJ46 = -$117,497
AJ13 = 56
E134 = 2
Payback_Percentage = 85%
Phase_I_Price = $40,000
PI_Ab_Rate = 2
Phase_I_Lots = 66
AL5 = Phase 2
AK5 = (empty - just a space that equals the " " value
tested above)
AK13 = 58

thanks for your help!

 

[dan]

thanks for teh suggestion. When I highlight the entire
cell formula and hit f9, the formula bar shows 0.9090909,
which is the correct result, but the spreadsheet still
shows 170% in the cell when I leave the cell.

I sent in the formula and vaules in another part of this
thread.

I'd be glad to email the entire workbook (1.3mb) if you
are interested to help you make best use of your time on
this.

thanks!

 

[Charles Williams]

Hi Dan,

The answer almost certainly lies in your use of circular calculations with
iteration. You may be able to test this by setting calculation to manual and
iteration to 1, then press F9 repeatedly: if the value cycles between 170%
and 91% then you have an unresolved circular calculation which does not
converge.

a crude example of this would be something like

a2=90-a4
a3=if(a4>0,a2+90,a2-90)
a4=90-a3

then put =a3 in a1 and =a3 in b3

The reason this happens is that when there are circular references and
iteration is enabled Excel calculates left-to-right, top-to-bottom until
convergence or max iters or max change. So depending on the exact sequence
of calculations you may appear to get multiple answers from the same cell:
one answer is from one iteration and the other answer is from the next.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Karl]

with all the ifs sorted it comes down to

=AK13+$E$134)/Phase_I_Lots

which is

(58+2)/66

= 0.909091

 

[Kevin Stecyk]

Hi Dan,

The answer almost certainly lies in your use of circular calculations with
iteration. You may be able to test this by setting calculation to manual and
iteration to 1, then press F9 repeatedly: if the value cycles between 170%
and 91% then you have an unresolved circular calculation which does not
converge.

a crude example of this would be something like

a2=90-a4
a3=if(a4>0,a2+90,a2-90)
a4=90-a3

then put =a3 in a1 and =a3 in b3

The reason this happens is that when there are circular references and
iteration is enabled Excel calculates left-to-right, top-to-bottom until
convergence or max iters or max change. So depending on the exact sequence
of calculations you may appear to get multiple answers from the same cell:
one answer is from one iteration and the other answer is from the next.

Hi Charles,

I received Dan's spreadsheet, and your description is exactly correct.
Dan's spreadsheet is large and reasonably complex. At present he is trying
to disable his circular calculation, and we'll see if we can reconfigure his
calculations to avoid a circular calc.

Just thought I would update you on your post.

Best regards,
Kevin

 

[Guest]

All,

Per Kevin's update, I'm trying to either get the circular
reference to converge for the inputs values in question
(primarily by just moving around some rows to change the
order in which Excel calculates the sheets), or
restructure the calculation to provide a valid answer
without a circular reference (perhaps using a linear
program using Solver).

Thanks to everyone's insightful help so far - I'll let
you know what I come up with, and if you have any
additional tips I'd appreciate it greatly.

 

[Charles Williams]

If this is a financial circular calculation (ie interest or inflation or
....) then you should restructure the calculation into more cells something
like

- calc period end amount excluding interest
- calc interest (compounded if required)
- calc period end amount plus interest

breaking it down into more steps usually resolves the circular reference or
enables you to see the logic problem.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm