Subtraction

R

Rookie

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.
 
G

Gary''s Student

Both your problems indicate the data in the cells is text rather than TRUE
numbers. Make sure the cells into which you are entering data is formatted
as General before entering the data.
 
N

Niek Otten

First question:

=H2-H3

Second:

You get #DIV?0! probably because the cells are empty. AVERAGE() skips empty cells, so it doesn't count them either which causes
the division by zero.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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.
 
D

David Biddulph

=H2-H3 to subtract

If =AVERAGE(H2,H3) is giving you #DIV/0! it sounds as if you haven't got
numbers in those cells. Perhaps you've got text? Look at =ISNUMBER(H2)
and =ISTEXT(H2). Perhaps you've got spaces or other non-printing
characters?
 
R

Rookie

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.
 
N

Niek Otten

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.
 
R

Rookie

It still didn't work. I was able to change the numbers but the formulas
didn't work. I
also cannot add these numbers.
 
N

Niek Otten

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.
| >
| >
| >
 
N

Niek Otten

<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.
| > | >
| > | >
| > | >
| >
| >
| >
 
R

Rookie

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.
 
R

Rookie

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.
 
N

Niek Otten

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.
| > > | > | >
| > > | > | >
| > > | > | >
| > > | >
| > > | >
| > > | >
| > >
| > >
| > >
 
R

Rookie

Thanks for all your help and timeliness in responding. I tried all the
suggestions and still no success. I couldn't figure out how to down TRIMALL
so I'm just going to use FOX for stats that I want to do calculations on.
Thanks again.
 
J

johr77

Thanks for all your help and timeliness in responding.  I tried all the
suggestions and still no success. I couldn't figure out how to down TRIMALL  
so I'm just going to use FOX for stats that I want to do calculations on.  
Thanks again.


TRY This =(VALUE(H3)+VALUE(H2))/2
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top