2

I'm using Excel 2007 for displaying results of various queries I get from TOAD/Oracle. Suddenly it dawned on me that the Auto Calculations on the bottom status bar are not working even though everything that should be ticked is ticked and configured.

When writing arbitrary numbers in another new sheet (of the same document) and selecting the corresponding cells, the sum is displayed correctly.

I spent a frustrating hour searching everywhere for why this is happening, enabling/disabling Add-ins and options, and decided to ask a question here.

I came to the conclusion that something is definitely wrong with the format of the data in the cells, or the way TOAD exports them.

robinCTS
  • 4,407
hello_earth
  • 223

2 Answers2

4

It's possible the data has been imported as text, rather than numerical values. Most Excel functions will treat text that looks like a number as a number, therefore skipping the problem, but it does appear to trip up the Auto Calculations.

To see if this is the problem, select some of the cells and apply a number format (CtrlShift1 is an easy shortcut for 0.00). If the cells remain unchanged, then they are text, rather than numbers.

To fix the problem, you might be able to change the TOAD/Oracle export settings or you can convert the text to numbers using the VALUE() function or paste the cells into a text editor and then back into Excel.

Mike Fitzpatrick
  • 17,427
1

If there is a cell in the range with an error, sums and other data won't compute. I had that happen when trying to get a quick sum on a column of 200 numbers. There was a division by 0 error in the middle. Once I deleted that cell, it was fine.

Ken Leone
  • 11