Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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. -- Gary''s Student - gsnu200768 "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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 "Rookie" wrote in message ... |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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
=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? -- David Biddulph "Rookie" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
Format an empty cell as General. Put the number 1 in it. EditCopy. Select your cells with the "numbers". EditPaste 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 "Rookie" wrote in message ... | 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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. EditCopy. Select your cells with the "numbers". EditPaste 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 "Rookie" wrote in message ... | 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
Try ToolsOptionsCalculation tab, check Automatic
-- Kind regards, Niek Otten Microsoft MVP - Excel "Rookie" wrote in message ... | 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. EditCopy. Select your cells with the "numbers". EditPaste 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 | | "Rookie" wrote in message ... | | 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. | | | |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
It was already checked automatic.
"Niek Otten" wrote: Try ToolsOptionsCalculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel "Rookie" wrote in message ... | 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. EditCopy. Select your cells with the "numbers". EditPaste 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 | | "Rookie" wrote in message ... | | 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. | | | |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
<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 "Rookie" wrote in message ... | It was already checked automatic. | | "Niek Otten" wrote: | | Try ToolsOptionsCalculation tab, check Automatic | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Rookie" wrote in message ... | | 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. EditCopy. Select your cells with the "numbers". EditPaste 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 | | | | "Rookie" wrote in message ... | | | 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. | | | | | | | | | |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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 "Rookie" wrote in message ... | It was already checked automatic. | | "Niek Otten" wrote: | | Try ToolsOptionsCalculation tab, check Automatic | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Rookie" wrote in message ... | | 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. EditCopy. Select your cells with the "numbers". EditPaste 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 | | | | "Rookie" wrote in message ... | | | 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. | | | | | | | | | |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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 "Rookie" wrote in message ... | It was already checked automatic. | | "Niek Otten" wrote: | | Try ToolsOptionsCalculation tab, check Automatic | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Rookie" wrote in message ... | | 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. EditCopy. Select your cells with the "numbers". EditPaste 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 | | | | "Rookie" wrote in message ... | | | 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. | | | | | | | | | |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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. EditCopy. Select your "numbers". EditPaste 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 he http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================== =============== -- Kind regards, Niek Otten Microsoft MVP - Excel "Rookie" wrote in message ... | 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 | | | "Rookie" wrote in message ... | | It was already checked automatic. | | | | "Niek Otten" wrote: | | | | Try ToolsOptionsCalculation tab, check Automatic | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "Rookie" wrote in message ... | | | 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. EditCopy. Select your cells with the "numbers". EditPaste | 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 | | | | | | "Rookie" wrote in message ... | | | | 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. | | | | | | | | | | | | | | | | | | |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
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. "Niek Otten" wrote: 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. EditCopy. Select your "numbers". EditPaste 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 he http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================== =============== -- Kind regards, Niek Otten Microsoft MVP - Excel "Rookie" wrote in message ... | 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 | | | "Rookie" wrote in message ... | | It was already checked automatic. | | | | "Niek Otten" wrote: | | | | Try ToolsOptionsCalculation tab, check Automatic | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "Rookie" wrote in message ... | | | 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. EditCopy. Select your cells with the "numbers". EditPaste | 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 | | | | | | "Rookie" wrote in message ... | | | | 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. | | | | | | | | | | | | | | | | | | |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtraction
On Feb 10, 11:33*am, Rookie wrote:
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. "Niek Otten" wrote: Hi Rookie, Always give as much information as you can, right away in the first post.. If had read that it's about websitedata, I'd given you my standard answer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtraction Chart | Excel Discussion (Misc queries) | |||
Cell subtraction | New Users to Excel | |||
subtraction formula | Excel Worksheet Functions | |||
subtraction formula | Excel Worksheet Functions | |||
Subtraction formula | Excel Discussion (Misc queries) |