Hi Rookie,
Always give as much information as you can, right away in the first post.
If had read that it's about website data, I'd given you my standard answer.
Here it is:
=================================================================
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
| Another reply to my own post. I just tried to copy/paste baseball stats from
| Fox Sports again pasting as HTML and all formulas worked perfectly. It
| appears that something is interferring from Yahoo.... I guess I'll use Fox
| but if you can figure out what the problem is with Yahoo stats, I'd be real
| interested in knowing. Thanks again.
|
| "Rookie" wrote:
|
| > I have copy/pasted stats from Yahoo baseball stats. I am trying to average 2
| > years of specific stats (ie: 2006 HR and 2007 HR for a single player). This
| > is where I get #DIV/0! if I try to do it with the numbers that were
| > copy/pasted from the internet (Using IMSUB to subtract I get the error #NUM!
| > and if I use SUM to add I just get a Zero). If I manually enter these same
| > numbers in any blank cells anywhere else on the same worksheet the formula
| > works fine. I just can't do it with the numbers that I copy/pasted from the
| > internet even if I recopy/paste them to a different cell. I have tried to
| > 'paste special' from the internet and have 3 options: HTML, Unicode Text, or
| > Text. I have to use HTML or they don't format correctly into columns. Since
| > I can use the formulas with numbers I enter myself I tend to believe it's the
| > way the data is entered into the worksheet not the program itself. I feel
| > there is something wrong with the copy/paste or something else is getting
| > pasted that I'm not aware of and is interfering with the execution of the
| > formulas. Like I said, I've tried SUM, IMSUB, and AVERAGE and they don't work
| > with the numbers pasted from the internet. I haven't tried any other
| > forumulas. Those are primarily the ones I would use. Thanks for you help.
| >
| > "Niek Otten" wrote:
| >
| > > <the formulas didn't work. I also cannot add these numbers>.
| > >
| > > What does that mean? What *exactly* do you do and what do you see happening?
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > >
| > > | It was already checked automatic.
| > > |
| > > | "Niek Otten" wrote:
| > > |
| > > | > Try Tools>Options>Calculation tab, check Automatic
| > > | >
| > > | > --
| > > | > Kind regards,
| > > | >
| > > | > Niek Otten
| > > | > Microsoft MVP - Excel
| > > | >
| > > | > | It still didn't work. I was able to change the numbers but the formulas
| > > | > | didn't work. I
| > > | > | also cannot add these numbers.
| > > | > | "Niek Otten" wrote:
| > > | > |
| > > | > | > Format an empty cell as General. Put the number 1 in it. Edit>Copy. Select your cells with the "numbers". Edit>Paste
| > > Special,
| > > | > | > check Multiply.
| > > | > | > Now the cells are real numbers. That is, if they don't contain spaces or other invisible characters.
| > > | > | > Post back if they do; that can be remedied too.
| > > | > | >
| > > | > | > --
| > > | > | > Kind regards,
| > > | > | >
| > > | > | > Niek Otten
| > > | > | > Microsoft MVP - Excel
| > > | > | >
| > > | > | > | In reply to my own post, I have found that I can use the AVERAGE formula and
| > > | > | > | IMSUB on a blank worksheet that I input the numbers manually. However, if I
| > > | > | > | copy/paste data into a worksheet I can no longer use those functions.
| > > | > | > | However, I can use ADD. I have checked the format of the cells and it
| > > | > | > | doesn't seem to matter if I have them formated as "general" or "number."
| > > | > | > | There's way too much data to input everything manually and that would defeat
| > > | > | > | the purpose of having excel. Can anyone help me overcome this obstacle?
| > > | > | > | Thanks.
| > > | > | > |
| > > | > | > | "Rookie" wrote:
| > > | > | > |
| > > | > | > | > I have been trying to subtract 2 simple numbers. I can do it by entering the
| > > | > | > | > formula =123-111 but would to make a formula where I could use the cell as a
| > > | > | > | > reference (ie H2-H3)
| > > | > | > | >
| > > | > | > | > I have also been trying to find the average of 2 numbers. I use
| > > | > | > | > =AVERAGE(H2,H3)
| > > | > | > | > I get #DIV/0! I can't seem to get it to divide by 2.
| > > | > | >
| > > | > | >
| > > | > | >
| > > | >
| > > | >
| > > | >
| > >
| > >
| > >
