Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula not working right
I have a formula in my spreadsheet that check a balance.
=IF(SUM(C17:H17)=I17,"OK", "ERR") Here are the values that are in each cell C17 104.86 D17 58.32 E18 -209.11 F19 0 G19 0 H19 52.78 I17 6.85 I get "ERR" because when I evaluate K17 where the formula is it gives me 6.84999999999999. I have tried everything I can think of to fix this with no luck. I even tried a new spreadsheet with the values and fromulas with no luck (I did not use copy and paste to get the info to the new sheet, typed it all in) Please help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula not working right
If the cells values listed (i.e. C17 and H17) are functions of other numbers
(i.e. C17 is actually "=SUM(C15:C16)") and the formatting of these cell display a numerical value of only 2 decimal places the function will still use the unrounded numbers. Example C15 = "4.862" C16 = "100.00" C17 = "SUM(C15:C16)" If the above was True the Sun of the fields is 104.862 However if the formatting for C17 was set for only 2 decimal places if will display 104.86 as you have provided below. Now if you use the C17 Cell in an equation as you are it will still use the 104.862, there for throwing the math off to not use the exact value. "justlearnin" wrote: I have a formula in my spreadsheet that check a balance. =IF(SUM(C17:H17)=I17,"OK", "ERR") Here are the values that are in each cell C17 104.86 D17 58.32 E18 -209.11 F19 0 G19 0 H19 52.78 I17 6.85 I get "ERR" because when I evaluate K17 where the formula is it gives me 6.84999999999999. I have tried everything I can think of to fix this with no luck. I even tried a new spreadsheet with the values and fromulas with no luck (I did not use copy and paste to get the info to the new sheet, typed it all in) Please help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula not working right
The formula is working fine. You're experiencing problems with numbers.
Computers generally store numbers as floating point in binary, base 2 which are approximations of decimal numbers, base 10. Some numbers in decimal can be represented exactly in binary and others cannot. There are many articles concerning floating point. Google IEEE floating point. You can address your problem by rounding the numbers or computing the difference as less than something like 0.000000001 to determine equality Modern computers don't work in decimal. Older ones did and did not have this problem. Progress, I guess. Tyro "justlearnin" wrote in message ... I have a formula in my spreadsheet that check a balance. =IF(SUM(C17:H17)=I17,"OK", "ERR") Here are the values that are in each cell C17 104.86 D17 58.32 E18 -209.11 F19 0 G19 0 H19 52.78 I17 6.85 I get "ERR" because when I evaluate K17 where the formula is it gives me 6.84999999999999. I have tried everything I can think of to fix this with no luck. I even tried a new spreadsheet with the values and fromulas with no luck (I did not use copy and paste to get the info to the new sheet, typed it all in) Please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula not working | Excel Worksheet Functions | |||
Formula not working | Excel Discussion (Misc queries) | |||
Formula not working | Excel Worksheet Functions | |||
formula is not working | Excel Worksheet Functions | |||
Formula not working | Excel Worksheet Functions |