Formulas aren't calculating correctly.

[rgillson3]

I'm having intermittant issues with basic "sum" formulas that are copied from another worksheet are not calculating correctly. In fact, they appear to be retaining the values from the original spreadsheet.

Recently, I've found that if I click the box next to "Enable iterative calculation" the calculation is corrected.

But, I still have two issues.

For one, the formulas don't always "act up".

And two, the "Enable iterative calculation" box becomes unclicked.

 

[rgillson3]

Actually, I have =SUM(O3:O19) in O19. =+O3+O4+O5+O6+O7+O8+O9+O10+O11+O12+O13+O14+O15+O16+O17 was originally a way to see the correct value result. However, that one now shows an incorrect/previous value as well.

 

[rgillson3]

It's occurring to me that it would be much easier to resolve this with a sample of the file in it's err state.

The next time it happens, I'll remove non-essential information (names & employee numbers) and upload it for review.

 

[Joe4]

Actually, I have =SUM(O3:O19) in O19

If that is indeed true, and not a typo, there is your circular reference right there!
You are putting the formula in cell O19 AND including a reference to cell O19 in the formula!
So the formula in the cell is trying to call the value in the same cell it exists in!
Do you understand the issue this causes?
Normally, you do NOT include the cell that the formula exists in within the formula in that cell!

 

[rgillson3]

Actually, I think they often are. Could it be as simple as closing the "source" spreadsheet? And, if so, why wouldn't the err occur more often?

 

[rgillson3]

I'm sorry that was a typo. The formula goes thru O18 and is located in O19.

I've closed down all instances of Excel and found a file that's returning the odd circular reference message.

It's been a while since I uploaded a file and don't seem to have the option to attach a file.

 

[Joe4]

It's been a while since I uploaded a file and don't seem to have the option to attach a file.

You have never been able to attach files in this forum. You can post images, but you cannot attach files.

If you wish to share files you must upload them to a file sharing site and them post the link here for us.

 

[rgillson3]

Ok. I must be confusing this site with another one.

I've just discovered a crisis at work. I'll have to try to upload the file at another time.