different formats

  • Thread starter Thread starter anthony
  • Start date Start date
A

anthony

I have 2 spreadsheets with data that both contain numbers,
but the formatting is such on one of them that I cannot
write any formulas to add the #'s on one sheet to the #'s
on the other sheet. I have verified with the =isnumber()
formula that both sets of numbers are formatted as numbers
and not text, but the result of adding the 2 #'s always
comes out #N/A. The format cells function and format
painter do not have any effect and do not change the
outcome of my formulas.

How do get the formats to match and formulas to work, and
how do I maintain those formats through my other
spreadsheets.

Thanks for any help!!
 
ISNUMBER doesn't tell you that a cell is formatted as a
number, it says that the value is a number. Your cell may
still be formatted as text. Even still, cells formatted
as text and containing numerical values can be summed, so
there's more to this situation than you've outlined.

Try using =VALUE(Sheet1!A1)+VALUE(Sheet2!A1) and see what
happens.
 
Hi Anthony,
You do not indicate where or how you are getting #N/A! errors.
Does this have anything to do with the use of VLOOKUP Worksheet
Formula, if so take a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

Changing the formatting of a cell does not make it become a number
until the value is reentered. The ISNUMBER Worksheet Function
will indicate whether Excel thinks it is a number or not.

Take a look at
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
and at
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat

If you have 12.34 in a cell and format it as 0 so that it show as
12, you have not changed the value to 12 you have only formatted
the cell so that it show no decimal places.

If you have a cell formatted as text and type a number into it, it
is still text. To make it a number you would first have to format
the cell as a number but nothing changes until you reenter the
value. F2 then Enter will reenter the value. You can also use
a macro to effect a change.

Sorry if this answer is all over the place, and perhaps may not
answer your question, but your question is not at all clear to me.
 
Back
Top