Formatting as number does not change text into a number.
Here's my standard checklist:
=================================================================
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
=================================================================
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| All cells are formatted as numbers and its still showing #N/A
|
| "Peo Sjoblom" wrote:
|
| > The reason you get a value error is either that VLOOKUP returns a text value
| > that you multiply with the content
| > in F22 or that the content in F22 is text (could be trailing spaces etc)
| >
| >
| > --
| >
| >
| > Regards,
| >
| >
| > Peo Sjoblom
| >
| >
| > | > > Tried that one, but then it just brings up #NAME?
| > >
| > > "Niek Otten" wrote:
| > >
| > >> Omit the quotes around Reg
| > >>
| > >> --
| > >> Kind regards,
| > >>
| > >> Niek Otten
| > >> Microsoft MVP - Excel
| > >>
| > >> message
| > >> | > >> | The formula is:
| > >> |
| > >> | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE)
| > >> |
| > >> | F22 is the total hours (result of a sum totalling all hours in the row)
| > >> | The table it is looking the information up from is
| > >> | Column 1 = Reg
| > >> | Column 2 = Hourly Cost
| > >> |
| > >> | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly
| > >> Costs are
| > >> | formatted as currency.
| > >> |
| > >> |
| > >> |
| > >> | "Dave" wrote:
| > >> |
| > >> | > Hi,
| > >> | > You probably don't need to email your sheet.
| > >> | > Just show us a copy of the formula you're using, and some details of
| > >> the
| > >> | > data table you're trying to look up.
| > >> | > Regards - Dave.
| > >> | >
| > >> | > "forevertrying" wrote:
| > >> | >
| > >> | > > Hi there,
| > >> | > >
| > >> | > > I am using VLOOKUP to calculate vehicle usage hours... or should I
| > >> say, I am
| > >> | > > TRYING to use.
| > >> | > >
| > >> | > > It keeps sending back a #VALUE! error and I have absolutely no idea
| > >> why. I
| > >> | > > have used all the help I can possible find, and I can't see where
| > >> its going
| > >> | > > wrong.
| > >> | > >
| > >> | > > I can e-mail my worksheet for someone to have a look at. I'm fairly
| > >> sure its
| > >> | > > a simple thing to fix, but, as I can't do it myself, I must be
| > >> simpler ;o)
| > >> | > >
| > >> | > > Thank you in advance
| > >> | > >
| > >> | > >
| > >>
| > >>
| > >>
| >
| >
| >
