Here's my standard list
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
=================================================================
Your Numbers don't behave (like numbers)
Niek Otten, May 11, 2006
Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc.
In short:
Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!
Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!
· Format an empty cell as Number. Enter the number 1 in it. Edit>Copy.
Select your "numbers". Edit>Paste Special, check Multiply. Hopefully your cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number
of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
function to remove them
· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function
to remove most of them
· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
David McRitchie's TRIMALL() function to remove them. It can be downloaded here:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
=================================================================
| Hi Max & John,
|
| I'd done all you suggested and checked again. Still, I get $0.00 in the
| cell and the formula result remains in the formula box.
|
| I have the cell formatted to 'currency.' All my other cells and calcs work
| fine.
|
| Kai
| San Diego
|
| "Max" wrote:
|
| > Likely that calc mode was inadvertently set to Manual here. Press F9, does it
| > compute properly? To check/change calc mode, click Tools > Options >
| > Calculation tab. Ensure "Automatic" is checked > OK.
| >
| > Along similar vein, another non-computing phenomena to watch out for is the
| > possibility is the cell with the formula might have been pre-formatted as
| > text, unknown to you of course, when you dropped the formula in. In such an
| > instance, just re-format the cell as either general or number (via Format >
| > Cells), then re-enter the formula by clicking inside the formula bar and
| > pressing ENTER. Note that re-formatting alone does not trigger calculation,
| > you need to re-enter the formula.
| > --
| > Max
| > Singapore
| >
http://savefile.com/projects/236895
| > xdemechanik
| > ---
| > "Kai" wrote:
| > > This is a phenomenonally simple task:
| > >
| > > All I want is the formula "=SUM(B38:F38)" result to display on my
| > > spreadsheet. Excel shows $0.00 in the cell. The formula result, however, is
| > > '$9288.72.' I am so frustrated!!!
| > >
| > > Thanks for any help with this prob.
37)