 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007What is wrong with this formula?
I get the opposite effect when I apply this formula... =IF(D75=D76,"Balanced","Error") The answer is "Error", even though the amount is exactly the same in D75 and D76. If I reverse it I still get the wrong answer... =IF(D75<>D76,"Error","Balanced" The answer is "Balanced" I must be missing some very basic syntax knowledge. The cells D75 and E76 are definitely the same number format. Thanks for your help. Not sure, it works for me. But as your last paragraph mentions D75 and E76, should the formula not be: =IF(D75=E76,"Balanced","Error") George Gee >I get the opposite effect when I apply this formula... > [quoted text clipped - 13 lines] > > Thanks for your help. It's probably a rounding issue. =IF(ROUND(D75,2)=ROUND(E76,2),"Balanced","Error") Biff > Not sure, it works for me. > But as your last paragraph mentions D75 and E76, [quoted text clipped - 21 lines] >> >> Thanks for your help. Sorry, that was a typo. I meant =IF( D75=D76),"Balanced,"Error") This really has be stumped. The format of D75 and D76 is a simple 2 decimal number. Could it be something to do with D75 and D76 each having a formula of its own? Thanks > Not sure, it works for me. > But as your last paragraph mentions D75 and E76, [quoted text clipped - 21 lines] >> >> Thanks for your help. Format both cells as General and make the column wide enough to display all decimals. You'll probably see they are not equal.  SignatureKind regards, Niek Otten Microsoft MVP - Excel | Sorry, that was a typo. I meant =IF( D75=D76),"Balanced,"Error") | [quoted text clipped - 29 lines] | >> | >> Thanks for your help. Sorry, that didn't work either. I am beginning to be a bore I'm afraid. The figures are all 2 decimal dollar amounts. No fractional calculations are involved at all. It is just a simple accounting workbook with one sheet for transaction detail and another sheet for summary amounts by G/L code.. D75 on the Summary worksheet has this formula =SUM(Debits)-SUM(Credits)-45550.14 - where Debits and Credits are on the Detail worksheet D76 on the Summary worksheet has this formula =SUM(D16+D45+(D52*-1)+(D53*-1)+(D54*-1)+(D55*-1)+(D56*-1)+(D60*-1)) - where these figures come from the Summary worksheet The result of the formulas are 4434.32 in D75 and 4434.32 in D76, which is correct. Then I enter this formula in cell D77 on the Summary worksheet =IF(D75=D76,"Balanced","Error") and it comes up "Error" Gr-r-r-r. > Format both cells as General and make the column wide enough to > display all decimals. [quoted text clipped - 37 lines] > | >> > | >> Thanks for your help. It must be rounding. Change the format of the cells to 0.000000000 and see if the numbers are still the same, and increase the number of decimal places, you will see them differ at some point. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Sorry, that didn't work either. I am beginning to be a bore I'm afraid. > [quoted text clipped - 61 lines] >> | >> >> | >> Thanks for your help. What do you get for =(D76-D77) Note that the parentheses are needed for this purpose. The issue is that computers do binary math, but in binary most terminating decimal fractions are non-terminating binary fractions that can only be approximated (just as 1/3 can only be approximated in decimal). When you do math with approximate inputs, it should be no surprise when the output is also only approximate. In fact the only 2-place decimal fractions that can be exactly represented are .00, .25, .50, and .75. Since you are only adding and subtracting numbers that each have no more than 2 decimal places, you can round the result to 2 decimal places without violence to the calculations and thus mask the impact of the binary approximations to the inputs. Excel will display nor more than 15 significant figures, which often makes it difficult to see where these differences are coming from. However, to get different results from =IF(D75=D76,"Balanced","Error") and =IF(D75<>D76,"Error","Balanced") at least one of D75:D76 should show a difference from 4434.32 when formatted to show 11 decimal places. Niek was on the right track, but the General format will show no more than 6 decimal places (regardless of column width) for numbers in this range. Jerry > Sorry, that didn't work either. I am beginning to be a bore I'm afraid. > [quoted text clipped - 63 lines] > > | >> > > | >> Thanks for your help. Try =IF(ABS(D75-D76)<0.001,"Balanced","Error") Like Jerry, I thnik you have an IEEE rounding issue here best wishes >I get the opposite effect when I apply this formula... > [quoted text clipped - 13 lines] > > Thanks for your help. Yes!!!! Jerry, using the ABS worked! It never occurred to me after years of working with Excel that when all cells were formatted as 2 decimal numbers there could still be a rounding error. Live and learn. Thank you all ever so much. Joan > Try =IF(ABS(D75-D76)<0.001,"Balanced","Error") > Like Jerry, I thnik you have an IEEE rounding issue here [quoted text clipped - 17 lines] >> >> Thanks for your help. You'd be better off "fixing" the problem at its source rather than downstream. Use Round in your formulas in cells D75 and D76. Biff > Yes!!!! Jerry, using the ABS worked! It never occurred to me after years > of working with Excel that when all cells were formatted as 2 decimal [quoted text clipped - 25 lines] >>> >>> Thanks for your help. Just to add... Applying a format to show just two decimal places doesn't change the value in the cell. > Yes!!!! Jerry, using the ABS worked! It never occurred to me after > years of working with Excel that when all cells were formatted as 2 [quoted text clipped - 25 lines] > >> > >> Thanks for your help. SignatureDave Peterson Hi All helpers, Just to finish this off... both the Round formula provided by Biff and the ABS formula provided by Jerry work perfectly. But no wonder it was hard to figure out. When I added a credit of .01 cent to the detail, the original formula worked. When I added a debit of one cent to the detail, the original formula did not work, but adding a debit of .02 cents made it work. If only the balancing figure had been anything other than 4434.32 I might never have discovered the secret of Excel binary calculations which you all have so generously provided. Thanks again, Joan >I get the opposite effect when I apply this formula... > [quoted text clipped - 13 lines] > > Thanks for your help. Just think how much you could embezzle if you had millions of these transactions! Biff > Hi All helpers, > [quoted text clipped - 30 lines] >> >> Thanks for your help. Superman III Dave SignatureA hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. > Just think how much you could embezzle if you had millions of these > transactions! [quoted text clipped - 35 lines] > >> > >> Thanks for your help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.