The exact same formula showing different values

**wildecoyote1966** · 12-30-2021, 11:25 PM

in the Amort Tab I have 2 exact copies of the same formula (Cells T21 and T22)and they show different values. Both cells are formatted the same and I don't understand why one cell shows Zero and the other a value of 2362.41. When I click on the Fx symbol for the Zero value it shows the value of 2362.41.

The same tab apparently has a loop so I will post that later

**curiouscat408** · 12-30-2021, 11:50 PM

You have circular references in the file. I don't know if that is the culprit. But it is the nature of a "circle" that it has no beginning and no end. Thus, Excel has to stop the cycle of calculations somewhere.

I don't know if that is the cause of the disparity between T21 and T22. But it could be.

In fact, if I "re-enter" T22 by selecting T22, pressing f2, then Enter, the result is the same as T21: exactly zero.

OTOH, if I select each cell and do the evaluation in the Formula Bar (click in the FB, then press f9; be sure to press Esc to restore the formula), the result is 2362.41472975258 for both T21 and T22.

I think that is evidence that the CR is indeed the root cause of the problem.

I suggest that you remove the circular references.

**wildecoyote1966** · 12-30-2021, 11:52 PM

I'll post the question about that loop thanks

**Gregb11** · 12-30-2021, 11:53 PM

You have Circular References going on in the AMORT sheet. I think that's what's messing it up.
(If you delete everything in column M, then it shows the the correct result.)

*** Sorry, the above posts weren't there when I first responded - I should have refreshed before posting. ***

**wildecoyote1966** · 12-31-2021, 12:12 AM

It's ok Greg. The spreed sheet is complicated hey. I'm trying to connect real time data (Tab Final) with the Amortization schedule in real time so it is always up to date. And because of the interest setup with the bank I have to calculate in per day (Amort Columns P, Q, R and S) then charge it at the end of the month and even then it varies with the bank. Hence Amort Column M which should = Final Column I. I do think the loop has something to do with Amort Column M but it works in Cells M22, M25, M28 and M31 so I don't know why it doesn't work further down the column.

**FDibbins** · 12-31-2021, 12:44 AM

Circ refs can cause all sorts of unexpected actions in a WB.

For info, a Circ Ref Error is where a formula in a cell references itself - either directly or indirectly.
In it's simplest form...
A1=+A1
or
A1=B1, but B1=A1

In its most complex form, it can be hidden inside dozens of referenced formulas.

Excel will attempt to show you where the error is (on the bottom left corner), and in simple errors, this is usually pretty spot on. But in more complex files, excel tries its best, but often misses the exact location by many levels.

A method I use to find the location is (1st SAVE the file, so you can recover it later) to delete the cell (or row/column if it is the same formula) that excel says is the culprit. If the error goes away, you have found the sourse. If it shows another location, you have not yet found the location, so repeat the delete process again. Keep doing that until the error message disappears.

Once you find the location, undo the last action and examine that formula to see if you can ID where the problem lies.

Good luck, this can be a tedious process.