Randomly Incorrect values being displayed from linked cells in the same worksheet and same workbook

[kevanr]

Hi there

I have built a very complex 10 year Budget model. It contains lots pf VBA code and macros which have all been working fine for a long time.

I am using Excel 365

I have now encountered a strange problem:
a) where only in year 5, month 49 onwards are a few values not updating correctly that are linked to other cells, but if you go view those correctly linked cells they show you a different value, and also
b) where a simple sum formula of 4 rows beneath the total is incorrect (see image)

I have lots of circular references but I am using iterative calculation and I have tried with many different numbers of iterations, from my default setting of 200 to up to 2000 but it makes no difference
I have deleted the cells and manually recreated the links and formulas but that didn't help either

It is simply mind blowing to see this.

I hope somebody out there is able to help me resolve this frustrating situation.

Many thanks

Kevan

 

[Anthony47]

Is Calculation set to Automatic?
(look in Menu /File /Options /Formulas)

 

[kevanr]

Is Calculation set to Automatic?
(look in Menu /File /Options /Formulas)

Hi Anthony, yes it is and I also repeatedly press F9 to recalculate

 

[Anthony47]

Select BG328:BG332, and check in the Status line which is the "Sum" of the selected cells.
If you don't have "Sum" (of the selected Cells) in the Status bar then right click on the Status bar and check "Sum" (othe options are Average, Count, Min, Max and few others)

I mean that what you see in those cells could be different from what the cell contains, due for exaample to custom formats

Also, is Circular reference error affecting any of the cells in BG327 to BG332?

 

[kevanr]

They are the same formats, I have copied and pasted them again to ensure
The sum of the highlighted cells on the status bar is correct, but the total is incorrect

 

[Anthony47]

You mean that the Sum in the status bar give the correct value, whereas the formula SUM(BG328:BG332) gives the wrong result?
Is there any Circular reference error affecting the range BG327 to BG332?

If Yes & No then save a copy of the file, shutdown the pc and reboot, then retry

 

[kevanr]

Yes to your 1st line
No to your 2nd line
I will try a complete shutdown and reboot and revert, thanks

 

[kevanr]

Yes to your 1st line
No to your 2nd line
I will try a complete shutdown and reboot and revert, thanks

Shut down made no difference

Below is the data it pulls from - note the simple formula in BI590 that renders the incorrect value? The same applies to BK590 and BM590

 

[Anthony47]

Use Menu /Formula /Evaluate formula to step through the calculation of =BH595, maybe you'll notice what goes wrong.
For a better analysis, modify your formula to =BH595+0

Is there any Circular reference error that affects the cells involved in the calculations?

 

[kevanr]

Hi Anthony, I eventually figured out where the circular reference was - thank you for your time and effort, much appreciated