Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is Excel reliable
I'm using excel for years but today I was shocked. almost died.
While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#2
|
|||
|
|||
What are the specific values that are being miss-calculated?
-- Gary's Student "ראובן" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#3
|
|||
|
|||
the values we 200 135 360 80 350 350
and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "ראובן" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#4
|
|||
|
|||
Sadly, I cannot duplicate your erroneous result. The version of EXCEL on my
computer, unfortunately, returns 1475. Please check that your SUM function covers all the desired inputs. -- Gary's Student "ראובן" wrote: the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "ראובן" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#5
|
|||
|
|||
I've never seen Excel format a cell as text without human assistance. Could
you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#6
|
|||
|
|||
Thank you
1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#7
|
|||
|
|||
In Excel 2002, and later versions, you can turn on the error checking
feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ראובן wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
|
|||
|
|||
thank you Debra
I applied your good advice it is eficient for small spreadsheets but could hardly help when my spreadsheet contains thousands of lines. thank you very much Reuven "Debra Dalgleish" wrote: In Excel 2002, and later versions, you can turn on the error checking feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ראובן wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#9
|
|||
|
|||
I'd bet you could sort based on the checkmark...maybe. I don't use Excel
2002, so I don't know if the checkmark is applied at the beginning or end of the number. If at the beginning, sorting should work. Correct those cells when they're all in a group, and then re-sort when done. "?????" wrote in message ... thank you Debra I applied your good advice it is eficient for small spreadsheets but could hardly help when my spreadsheet contains thousands of lines. thank you very much Reuven "Debra Dalgleish" wrote: In Excel 2002, and later versions, you can turn on the error checking feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ????? wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#10
|
|||
|
|||
Maybe not, but if you are taking data from an import then it goes without
saying that you should do some data cleansing first. There are numerous ways of checking whether or not a set of data is numeric or not, one of which is a simple =COUNTA(Rng)-COUNT(Rng) If all your data is supposed to be numeric then this should be 0. There are easy ways of flagging/fixing data that is supposed to be numeric but is not, but the onus is on you to do some of the error checking up front. Excel is generally as reliable as any other tool in as much as garbage in means garbage out. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "?????" wrote in message ... thank you Debra I applied your good advice it is eficient for small spreadsheets but could hardly help when my spreadsheet contains thousands of lines. thank you very much Reuven "Debra Dalgleish" wrote: In Excel 2002, and later versions, you can turn on the error checking feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ????? wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#11
|
|||
|
|||
<<<"I've never seen Excel format a cell as text without human assistance"
Depends on what you consider "human assistance". New sheet Format A1 to Text. In A6, enter: =SUM(A1:A5) Notice ... the zero is left justified. Format of A6 is NOW Text ! Enter numbers in A1:A5, and return total of A2:A5. A6 is STILL Text! But, since the formula *IS* working (not displayed in the cell as a text string), and the contents of A6 equate to True to Isnumber(), then A6 *HAD* to be General at the time of formula entry, BUT changed after referencing (duplicating the format of the *first* cell) the range. Try it with $'s or %'s. Now, I would consider that "without human assistance".<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Doug Kanter" wrote in message ... I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#12
|
|||
|
|||
I stand corrected! This is one I hadn't seen.
"Ragdyer" wrote in message ... <<<"I've never seen Excel format a cell as text without human assistance" Depends on what you consider "human assistance". New sheet Format A1 to Text. In A6, enter: =SUM(A1:A5) Notice ... the zero is left justified. Format of A6 is NOW Text ! Enter numbers in A1:A5, and return total of A2:A5. A6 is STILL Text! But, since the formula *IS* working (not displayed in the cell as a text string), and the contents of A6 equate to True to Isnumber(), then A6 *HAD* to be General at the time of formula entry, BUT changed after referencing (duplicating the format of the *first* cell) the range. Try it with $'s or %'s. Now, I would consider that "without human assistance".<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Doug Kanter" wrote in message ... I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven |
#13
|
|||
|
|||
This raises a question (for me, at least). I import lots of data from
customers, and some of it looks like it was tossed into a lunch bag and shaken. I clean it up using scripts in Paradox, the database, because within the Paradox language, there's a function called SCAN, which tells a script to look at each record and perform whatever procedure you've programmed. Is there something similar with VBA, to tell Excel to go down a list of records and do whatever? |
#14
|
|||
|
|||
In , Doug Kanter told us an
interesting story. My reply to this story is at the bottom of this message. This raises a question (for me, at least). I import lots of data from customers, and some of it looks like it was tossed into a lunch bag and shaken. I clean it up using scripts in Paradox, the database, because within the Paradox language, there's a function called SCAN, which tells a script to look at each record and perform whatever procedure you've programmed. Is there something similar with VBA, to tell Excel to go down a list of records and do whatever? If you write it yourself, yes. -- Amedee Van Gasse |
#15
|
|||
|
|||
"Amedee Van Gasse" wrote in message ... In , Doug Kanter told us an interesting story. My reply to this story is at the bottom of this message. This raises a question (for me, at least). I import lots of data from customers, and some of it looks like it was tossed into a lunch bag and shaken. I clean it up using scripts in Paradox, the database, because within the Paradox language, there's a function called SCAN, which tells a script to look at each record and perform whatever procedure you've programmed. Is there something similar with VBA, to tell Excel to go down a list of records and do whatever? If you write it yourself, yes. That was the natural assumption here. Can you be any more specific? |
#16
|
|||
|
|||
Thank you Ken.
Unfortunately the count and counta return the same result. They both counted the non numeric values. So this solution did not work But I think that all the persons responded to my message ignored the fact that these values were numbers and non numbers at the same time: They were valid for multiplication and at the same time invalid for the Sum function. This inconsistency is the problem!!! Reuven "Ken Wright" wrote: Maybe not, but if you are taking data from an import then it goes without saying that you should do some data cleansing first. There are numerous ways of checking whether or not a set of data is numeric or not, one of which is a simple =COUNTA(Rng)-COUNT(Rng) If all your data is supposed to be numeric then this should be 0. There are easy ways of flagging/fixing data that is supposed to be numeric but is not, but the onus is on you to do some of the error checking up front. Excel is generally as reliable as any other tool in as much as garbage in means garbage out. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "?????" wrote in message ... thank you Debra I applied your good advice it is eficient for small spreadsheets but could hardly help when my spreadsheet contains thousands of lines. thank you very much Reuven "Debra Dalgleish" wrote: In Excel 2002, and later versions, you can turn on the error checking feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ????? wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#17
|
|||
|
|||
Try that =count() vs =counta() once more.
If they turn out to be equal, then either there's nothing in those cells or the "text" numbers have already been converted to "number" numbers. And for the inconsistency part--I think it depends on what your definition of inconsistency is. Excel is consistent in the way it treats "text" numbers--it always does the same thing when you do the same thing. Excel does try to help you by coercing "text" numbers to "number" numbers in some cases and ignores it in others. But that behavior is consistent within excel's rules. (It may not be what you expect, but it is consistent.) ======= I think that the onus lies with the user/developer to make sure the data is correct--just like any other computer program--heck, just like everything in life. I can't put dirty dishes in the clothes washer and expect that to be consistent with the dish washer. ????? wrote: Thank you Ken. Unfortunately the count and counta return the same result. They both counted the non numeric values. So this solution did not work But I think that all the persons responded to my message ignored the fact that these values were numbers and non numbers at the same time: They were valid for multiplication and at the same time invalid for the Sum function. This inconsistency is the problem!!! Reuven "Ken Wright" wrote: Maybe not, but if you are taking data from an import then it goes without saying that you should do some data cleansing first. There are numerous ways of checking whether or not a set of data is numeric or not, one of which is a simple =COUNTA(Rng)-COUNT(Rng) If all your data is supposed to be numeric then this should be 0. There are easy ways of flagging/fixing data that is supposed to be numeric but is not, but the onus is on you to do some of the error checking up front. Excel is generally as reliable as any other tool in as much as garbage in means garbage out. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "?????" wrote in message ... thank you Debra I applied your good advice it is eficient for small spreadsheets but could hardly help when my spreadsheet contains thousands of lines. thank you very much Reuven "Debra Dalgleish" wrote: In Excel 2002, and later versions, you can turn on the error checking feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ????? wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
#18
|
|||
|
|||
Thank you Dave
1. I'm sorry. count and counta gave the same result (I can send the sample to you if you wish). 2. The inconsistency is that excel does not treat some value in the same way: If you multiply "abc" by 2 you get #Value. In my case Sum (200, 100) was 100 (ISNUMBER returned false on the 200) but 200*2 was 400 So 200 was numeric and non numeric at the same time. How would you call it? Reuven "Dave Peterson" wrote: Try that =count() vs =counta() once more. If they turn out to be equal, then either there's nothing in those cells or the "text" numbers have already been converted to "number" numbers. And for the inconsistency part--I think it depends on what your definition of inconsistency is. Excel is consistent in the way it treats "text" numbers--it always does the same thing when you do the same thing. Excel does try to help you by coercing "text" numbers to "number" numbers in some cases and ignores it in others. But that behavior is consistent within excel's rules. (It may not be what you expect, but it is consistent.) ======= I think that the onus lies with the user/developer to make sure the data is correct--just like any other computer program--heck, just like everything in life. I can't put dirty dishes in the clothes washer and expect that to be consistent with the dish washer. ????? wrote: Thank you Ken. Unfortunately the count and counta return the same result. They both counted the non numeric values. So this solution did not work But I think that all the persons responded to my message ignored the fact that these values were numbers and non numbers at the same time: They were valid for multiplication and at the same time invalid for the Sum function. This inconsistency is the problem!!! Reuven "Ken Wright" wrote: Maybe not, but if you are taking data from an import then it goes without saying that you should do some data cleansing first. There are numerous ways of checking whether or not a set of data is numeric or not, one of which is a simple =COUNTA(Rng)-COUNT(Rng) If all your data is supposed to be numeric then this should be 0. There are easy ways of flagging/fixing data that is supposed to be numeric but is not, but the onus is on you to do some of the error checking up front. Excel is generally as reliable as any other tool in as much as garbage in means garbage out. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "?????" wrote in message ... thank you Debra I applied your good advice it is eficient for small spreadsheets but could hardly help when my spreadsheet contains thousands of lines. thank you very much Reuven "Debra Dalgleish" wrote: In Excel 2002, and later versions, you can turn on the error checking feature, and set it to mark cells with numbers stored as text: Choose ToolsOptions On the Error tab, add a check mark to 'Enable background error checking' Add a check mark to 'Numbers stored as text' Click OK ????? wrote: Thank you 1. After analysis I found that the first two values were not numbers (applying the ISNUMBER function resulted as False). 2. I got the file from a colleague who was not aware to the problem. 3. But my problem is that I could multiply "non numbers" and get a correct result (and this supposed to be a tool to verify if these values were numbers), and at the same time the "SUM" function ignored them. Who knows how many times we had summarized "Non Numeric" numbers without being aware that we had a problem. I know how to fix it this time, but I'm afraid that every time I will use excel, it will be required to verify that no "Non Numbers" infected my spreadsheets. thank you "Doug Kanter" wrote: I've never seen Excel format a cell as text without human assistance. Could you spreadsheet have been a victim of slippery fingers, perhaps before enough cups of coffee in the morning? "?????" wrote in message ... the values we 200 135 360 80 350 350 and the summay was: 1140 the first two values were not included in the Sum. reuven "Gary's Student" wrote: What are the specific values that are being miss-calculated? -- Gary's Student "?????" wrote: I'm using excel for years but today I was shocked. almost died. While summarizing a column of numbers, the summary was incorrect. I suspected that few cells contained text rather than numbers so I multiplied every cell in the column and got the correct number. So I assume that sometimes excel ignores cells and sometimes considers them. What if the numbers are significant amounts of money? How can I rely on excel? I will be happy to send this little sheet to you. do i have to crosscheck every spreadsheet i use? reuven -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
#19
|
|||
|
|||
"ראובן" wrote:
1. I'm sorry. count and counta gave the same result (I can send the sample to you if you wish). In all versions of Excel, if A1:A2 appears to contain 200 and 100 but SUM(A1:A2) returns 100, then COUNT(A1:A2) will return 1 but COUNTA(A1:A2) will return 2. What you did is to get the results you claim is a mystery. If you post your e-mail address, I will e-mail you privately. 2. The inconsistency is that excel does not treat some value in the same way: If you multiply "abc" by 2 you get #Value. In my case Sum (200, 100) was 100 (ISNUMBER returned false on the 200) but 200*2 was 400 So 200 was numeric and non numeric at the same time. How would you call it? For bivariate math operations, Excel consistently tries to coerce values into numbers. Thus ="abc"*2 will return #VALUE and ="200"*2 will return 400 because "200" can be coerced into a number and "abc" cannot. For range functions, such as SUM, excel coerces nothing; if you have a text string of digits in a cell, they will be ignored. A simple way to use this to prep your data is to place a zero in a blank cell, copy that cell then select the imported data range and Edit|Paste Special|Add. All text "numbers" will be coerced to numeric values; all non-coerceable text will become error values. After taking note of the error locations, you can undo and then repeat the process excluding the non-coerceable cells if you want to leave them as imported. Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |