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.

 

[Joe4]

Is your calculation mode set to "automatic" or "manual"?
Do any of your formulas cause circular references (i.e. the SUM formulas include the range that the are found in)?

 

[rgillson3]

The calculation mode is set to "automatic".

Before clicking on "Enable iterative calculation", I'm often getting a circular reference err that says it can't be shown. I can never find said err.

 

[RoryA]

The circular reference error is almost certainly what is causing the problem with the formulas, so you need to find what is causing that.

 

[rgillson3]

Normally I would agree. However, I don't find any circular references. What's more, once I click on "Enable iterative calculation", that err message no longer pops up.

 

[Joe4]

What's more, once I click on "Enable iterative calculation", that err message no longer pops up.

That is because that is the "override" button (so to speak) that allows circular references.

What I would recommend doing is starting with a cell that is not returning your expected result.
What cells is the formula in that cell referencing? Is the cell the formula is located in itself included in the formula?
If not, then look at the cells it is referencing, and check them to see what cells that they are referencing.
Keep working the trail back until you find it.
And be aware of cells referencing cells used previously in the formulas.

 

[rgillson3]

Oh, ok. I wasn't aware of that.

My incorrect calculation issue occurs in both a simple "sum" formula an done that is "a1+a2+a3".

 

[Joe4]

What cell is this formula in?
Please show us the formula, EXACTY as it appears.

What is in cells A1, A2, and A3?
If any contain formulas, what are the formulas?

 

[rgillson3]

As luck would have it, I'm not getting the err now.

But, the "basic" formula I have is =+O3+O4+O5+O6+O7+O8+O9+O10+O11+O12+O13+O14+O15+O16+O17 and is located in O18.

When the spreadsheets are acting up, this will reflect the total from the "source" spreadsheet. As you can see, there are no external spreadsheet references.

 

[Joe4]

But, the "basic" formula I have is =+O3+O4+O5+O6+O7+O8+O9+O10+O11+O12+O13+O14+O15+O16+O17 and is located in O18.

Why not just use this instead?

=SUM(O3:O19)

When the spreadsheets are acting up, this will reflect the total from the "source" spreadsheet. As you can see, there are no external spreadsheet references.

Are the external spreadsheet closed when you get the error/unexpected results?