Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a known excel error?? Sequential drag down & IF ERRORS??
I have several errors that occurs in both excel 2000 and excel 2003
the foundation .. cells from b2 to g2 are formatted in green and with 2 digit number formats with neg numbers in brackets. H2 is formatted in yellow and contains the formula, and also the same number format. problem #1. in cell f2 for example, I will put in a number 150.00, then in cell f3 the new number is 150.01. I then will highlight the two numbers and drag down to say 148.00. The program will properly sequence the numbers up (or down for that matter) in the two digit readout format properly. What really takes the cake is in the formula box. The numbers are not consistantly in whole integers. Sometimes I have whole integers, but on the whole I am getting numbers that has anywhere from 9 to 12 zeros after with a 2 or a 7 attached to the end. eg. will be to see this number 150.00 151.0100000000002 151.020000000000002 and some attach a lot of 9's after. Now when I highlight two cells of 149.00 and drag down the intergers are in two decimal places . Problem #2 I am doing a simple checkbook style program with two negative entry columns for business , one column for being paid the day of service. The second column for any transaction after that date, similar to accounts receivables.. My twist on this was a math checker in the last column to check our math, if the numbers are correct it will say "match". The formula! In cell H2 enter =if(g2+c2-d2-e2=f2,"match",g2+c2-d2-f2). So, I enter the number 149.00 in cell e2, then 0 in f2, and 149 in g2 I get "match". So, far so good. Now, I put in 149.00in e3, and .01 in f3 and 149.01 in g3 and I get a red (0.00). the next penny up will give me a black (0.00)., and so on but some numbers for example such as 2.12, will give me a "match" . I found that retyping the numbers that have long decimal places such as 151.580000000002 to 151.58 will give me a (0.00). and 151.590000000002 into 151.59 will return "match". |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a known excel error?? Sequential drag down & IF ERRORS??
Excel (like almost all software) does binary math, not decimal math. Most
terminating decimal fractions (including 0.01) are non-terminating binary fractions that must be approximated (just as 1/3 must be approximated in decimal). Excel does correct math on these approximate inputs, and naturally arrives at an answer that is only approximately correct. The usual approach in these situations is to round all results to to 2 decimal places, to synch up the approximations to different calculations. For problem #2, approximations that are slightly larger or smaller than your intended number will tend to balance out, keeping the overall level of approximation small. For problem #1, 150.01 gets approximated as 1.50009999999999990905052982270717620849609375 so every term is calculated using an increment that is a little smaller than you intended. The result is that the degree of under-approximation increases with the number of cells. An alternative to rounding each term would be to avoid the approximation until the end in each cell, with a formula like =(15000-2+ROW())/100 You might find the VBA functions at http://groups.google.com/group/micro...06871cf92f8465 to be useful if you want to learn more about this topic. Jerry "trucat" wrote: I have several errors that occurs in both excel 2000 and excel 2003 the foundation . cells from b2 to g2 are formatted in green and with 2 digit number formats with neg numbers in brackets. H2 is formatted in yellow and contains the formula, and also the same number format. problem #1. in cell f2 for example, I will put in a number 150.00, then in cell f3 the new number is 150.01. I then will highlight the two numbers and drag down to say 148.00. The program will properly sequence the numbers up (or down for that matter) in the two digit readout format properly. What really takes the cake is in the formula box. The numbers are not consistantly in whole integers. Sometimes I have whole integers, but on the whole I am getting numbers that has anywhere from 9 to 12 zeros after with a 2 or a 7 attached to the end. eg. will be to see this number 150.00 151.0100000000002 151.020000000000002 and some attach a lot of 9's after. Now when I highlight two cells of 149.00 and drag down the intergers are in two decimal places . Problem #2 I am doing a simple checkbook style program with two negative entry columns for business , one column for being paid the day of service. The second column for any transaction after that date, similar to accounts receivables.. My twist on this was a math checker in the last column to check our math, if the numbers are correct it will say "match". The formula! In cell H2 enter =if(g2+c2-d2-e2=f2,"match",g2+c2-d2-f2). So, I enter the number 149.00 in cell e2, then 0 in f2, and 149 in g2 I get "match". So, far so good. Now, I put in 149.00in e3, and .01 in f3 and 149.01 in g3 and I get a red (0.00). the next penny up will give me a black (0.00)., and so on but some numbers for example such as 2.12, will give me a "match" . I found that retyping the numbers that have long decimal places such as 151.580000000002 to 151.58 will give me a (0.00). and 151.590000000002 into 151.59 will return "match". |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a known excel error?? Sequential drag down & IF ERROR
"Duke Carey" wrote: Read Chip Pearson's article about floating point math http://www.cpearson.com/excel/rounding.htm "trucat" wrote: I have several errors that occurs in both excel 2000 and excel 2003 the foundation . cells from b2 to g2 are formatted in green and with 2 digit number formats with neg numbers in brackets. H2 is formatted in yellow and contains the formula, and also the same number format. problem #1. in cell f2 for example, I will put in a number 150.00, then in cell f3 the new number is 150.01. I then will highlight the two numbers and drag down to say 148.00. The program will properly sequence the numbers up (or down for that matter) in the two digit readout format properly. What really takes the cake is in the formula box. The numbers are not consistantly in whole integers. Sometimes I have whole integers, but on the whole I am getting numbers that has anywhere from 9 to 12 zeros after with a 2 or a 7 attached to the end. eg. will be to see this number 150.00 151.0100000000002 151.020000000000002 and some attach a lot of 9's after. Now when I highlight two cells of 149.00 and drag down the intergers are in two decimal places . Problem #2 I am doing a simple checkbook style program with two negative entry columns for business , one column for being paid the day of service. The second column for any transaction after that date, similar to accounts receivables.. My twist on this was a math checker in the last column to check our math, if the numbers are correct it will say "match". The formula! In cell H2 enter =if(g2+c2-d2-e2=f2,"match",g2+c2-d2-f2). So, I enter the number 149.00 in cell e2, then 0 in f2, and 149 in g2 I get "match". So, far so good. Now, I put in 149.00in e3, and .01 in f3 and 149.01 in g3 and I get a red (0.00). the next penny up will give me a black (0.00)., and so on but some numbers for example such as 2.12, will give me a "match" . I found that retyping the numbers that have long decimal places such as 151.580000000002 to 151.58 will give me a (0.00). and 151.590000000002 into 151.59 will return "match". Thank you Duke Good article |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a known excel error?? Sequential drag down & IF ERROR
"Jerry W. Lewis" wrote: Excel (like almost all software) does binary math, not decimal math. Most terminating decimal fractions (including 0.01) are non-terminating binary fractions that must be approximated (just as 1/3 must be approximated in decimal). Excel does correct math on these approximate inputs, and naturally arrives at an answer that is only approximately correct. The usual approach in these situations is to round all results to to 2 decimal places, to synch up the approximations to different calculations. For problem #2, approximations that are slightly larger or smaller than your intended number will tend to balance out, keeping the overall level of approximation small. For problem #1, 150.01 gets approximated as 1.50009999999999990905052982270717620849609375 so every term is calculated using an increment that is a little smaller than you intended. The result is that the degree of under-approximation increases with the number of cells. An alternative to rounding each term would be to avoid the approximation until the end in each cell, with a formula like =(15000-2+ROW())/100 You might find the VBA functions at http://groups.google.com/group/micro...06871cf92f8465 to be useful if you want to learn more about this topic. Jerry "trucat" wrote: I have several errors that occurs in both excel 2000 and excel 2003 the foundation . cells from b2 to g2 are formatted in green and with 2 digit number formats with neg numbers in brackets. H2 is formatted in yellow and contains the formula, and also the same number format. problem #1. in cell f2 for example, I will put in a number 150.00, then in cell f3 the new number is 150.01. I then will highlight the two numbers and drag down to say 148.00. The program will properly sequence the numbers up (or down for that matter) in the two digit readout format properly. What really takes the cake is in the formula box. The numbers are not consistantly in whole integers. Sometimes I have whole integers, but on the whole I am getting numbers that has anywhere from 9 to 12 zeros after with a 2 or a 7 attached to the end. eg. will be to see this number 150.00 151.0100000000002 151.020000000000002 and some attach a lot of 9's after. Now when I highlight two cells of 149.00 and drag down the intergers are in two decimal places . Problem #2 I am doing a simple checkbook style program with two negative entry columns for business , one column for being paid the day of service. The second column for any transaction after that date, similar to accounts receivables.. My twist on this was a math checker in the last column to check our math, if the numbers are correct it will say "match". The formula! In cell H2 enter =if(g2+c2-d2-e2=f2,"match",g2+c2-d2-f2). So, I enter the number 149.00 in cell e2, then 0 in f2, and 149 in g2 I get "match". So, far so good. Now, I put in 149.00in e3, and .01 in f3 and 149.01 in g3 and I get a red (0.00). the next penny up will give me a black (0.00)., and so on but some numbers for example such as 2.12, will give me a "match" . I found that retyping the numbers that have long decimal places such as 151.580000000002 to 151.58 will give me a (0.00). and 151.590000000002 into 151.59 will return "match". Thank you Jerry, Wish excel had put in a fix for this, it makes an added problem to repair, whenever an unequal match occurs when it is really equal and a match in our formula. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a known excel error?? Sequential drag down & IF ERROR
"trucat" wrote:
Thank you Jerry, You're welcome. Wish excel had put in a fix for this, My point was that a general fix is not possible because the available precision will still be finite. To put it in the decimal arena where hopefully your intuition is better, what should be the fix for the fact that 0.3333 is not 1/3? If software were to assume that whenever 0.3333 is encountered, that 1/3 was intended, wouldn't that cause problems when it wasn't intended? Moreover in some calculations you would need to consider 0.333 or even 0.33 to be equivalent to 1/3; where would you stop? The best solution is an informed user base who can then take the most appropriate action for their particular circumstance. In Excel 97, MS did try to "fix" it in that fashion http://support.microsoft.com/kb/78113 but IMHO it causes more confusion than it avoids. Jerry |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a known excel error?? Sequential drag down & IF ERROR
Thanks again. I'm back to playing beginner excel programming trucat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Cannot Open Excel getting error: EXCEL.exe has generated errors .. | Excel Discussion (Misc queries) | |||
In excel how set up a sequence to drag Mon, Tues, Wed Etc, | Excel Discussion (Misc queries) | |||
Excel Program Error - Excel.exe Generated Errors Message | Setting up and Configuration of Excel | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |