#DIV/0 from AVG(20+21+18) Yes, I have a screenshot

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Screenshot of a simple average (20+21+18) becoming a #DIV/0! error:

www.sheathingtechnologies.com/images/divby0.jpg

Please note the formula bar, showing the formula in the #DIV/0! cell.
Please note that all these cells are formatted as numbers. Please note that
I have rebooted the whole computer, and the problem remains.

The actual spreadsheet is here:
http://www.sheathingtechnologies.com/divby0.xls

Out of curiosity: what the heck is going on?

This is from Office 2003 (either Pro or Small Business, I'm not sure)
running on Windows XP Pro.

Thanks,
J. Downing
Sheathing Technologies, Inc
 
The values in D19:D21 are text values. Yes, the cell may be formatted as a
Number, but until the data is re-entered, it remains Text. You can use the
ISNUMBER and ISTEXT functions to test this.

This happens sometimes, especially with imported data, where changing the cell
format will not affect the existing data until it is re-entered.

With only 3 values, you can easily just hit F2 then ENTER on each cell, but
if you had a particularly large range of data to "fix", you could follow
these steps:

Enter a 1 into any blank cell
Copy that cell
Select your range of data to fix
From the Data Menu, select "Paste Special..."
Check the "Values" and "Multiply" options
Click OK
Delete the one you entered originally

This effectively forces Excel to re-evaluate all of the data and by
multiplying by 1, you convert anything that can be recognized as a number to
a number without changing the value.

HTH,
Elkar
 
Back
Top