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
|
|||
|
|||
<<<"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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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? |
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) |