The “TRUNC” Function in Excel

90 pts.
Tags:
Excel 2003
Excel functions
Microsoft Excel
I used the "trunc" function this way "=TRUNC((H41-(I41*25))/10,9)", where H41is "=(A41-INT(A41))*100" and I41 is "=TRUNC(H41/25)".  A41 is 346.95.

With the Excel 2003 version on my computer, the result is 1.999999999; when, in fact, the result should be 2.

What am I doing wrong?  Or is there a fault with Excel?



Software/Hardware used:
Microsoft Excel 2003
ASKED: February 16, 2010  2:30 AM
UPDATED: April 5, 2010  3:16 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

It appears that you want the ROUND function instead of the TRUNC function. Truncating a number actually “shaves” off the numbers instead of rounding. In the formula you are telling it to have 9 spaces after the decimal that is why you have nine 9′s.

-Denwood

————————-

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Are you sure the value in A41 is in fact 346.95 and not something like 346.9499999 (but showing as 346.95 because of the cell formatting) ?
    65,110 pointsBadges:
    report
  • Whabby
    Regarding your answer, Carlosdl, 346.95 is an entered number. It is not a result of any formula or anything else. The formatting for the cell does not interfere with the contents of the cell. The number was entered as 346.95. Can you help me?
    90 pointsBadges:
    report
  • carlosdl
    Denwood, that was my first thought too, but the final division in this case is 20/10, so, even when Whabby is using a '9' as the second parameter for the TRUNC function, the result should be 2 (exactly).
    65,110 pointsBadges:
    report
  • Whabby
    Thank you Denwood for your contribution. However, Carlosdl has already expressed my sentiments exactly on FEB 17, 2010 at 10:11PM GMT. The answer in the formula should be 2.00000, and using the "TRUNC" function with the formula result, Excel should have truncated the 0's.
    90 pointsBadges:
    report
  • Whabby
    This is more serious than I thought. Consider this: If you enter the following simple formula into EXCEL 2003: =(346.95-346)*100, and formatted to show 13 decimal points, you would come to a determination that the program is faulty.. EXCEL 2003 returns 94.9999999999989, when the answer should be 95.0000000000000. What can be done to correct this problem?
    90 pointsBadges:
    report
  • carlosdl
    Interesting. Another example: =(127.95-127)*100 gives 95.0000000000003 But if you use the cell containing this formula to perform some calculations, the results are correct, so it seems to be just a display problem.
    65,110 pointsBadges:
    report
  • Whabby
    Take: =(127.95-127)*100 Result: 95.0000000000003 According to you, Carlosdl, the real number is 95.0000000000000 and Excel is just displaying it wrongly. Let's do a calculation, then. Take the result that Excel is returning and add 0.0000000000001. The result is 95.0000000000004. According to your theory Excel should return 95.0000000000001 but is displaying the wrong figure. Be that as it may, if it is a calculation problem with Excel or if it is a display problem with Excel, something must be done to remedy the situation.
    90 pointsBadges:
    report
  • Chippy088
    In my excel sheet, it does show 2 in the result cell. I did it this way. result H41 l41 a41 1.999999999 95 3 346.95 (I left the formating to all cells to excel. it shows the 1.999999999 here, but my sheet shows it as 2. Column headings are your references.) Check your formating, and whether excel is using the decimal portion of the previous cell results in your calc. What is displayed, is not always what is used in the calculations. I would try looking at 'INT' if you just want to use the integer portion of the cell result. Hope this helps
    4,625 pointsBadges:
    report
  • Whabby
    I'm sorry, Chippy088, your contribution does't help. You see, the discussions of the original question have advanced beyond the original question, especially from Feb 26, 2010 onward. I invite you to read all the advanced discussions and then after you would have had a chance to evaluate what was said, then join the more advanced discussions. We welcome it. However, on your sheet, Excel is returning 2 because you failed to format the cell to show 9 decimal places. You are not showing enough decimal places. Furthermore, the original problem arose because Excel is using what is not displayed (through formatting) to perform calculations. And I contend that what is not displayed is erroneous. Additionally, using the INTEGER (INT) function is no help because I contend that there is a calculation problem with EXCEL, not a display problem or a function problem or anything else (yet), but a fundamental calculation problem.
    90 pointsBadges:
    report
  • carlosdl
    Whabby, I have updated the answer. Please take a look.
    65,110 pointsBadges:
    report
  • Chippy088
    Ok, if you do not understand basic excel functions and formating, then try reading up on it. What you have asked for has been answered. It is not an excel fault but a user fault. i.e. you do not know what you are talking about,or I do not understand your english. If you insist on using the wrong functions then i am not suprised that it will not do what you want. You asked for our help, if you will not take it then why bother to ask......
    4,625 pointsBadges:
    report
  • Whabby
    I have two things to say: Firstly, I am sorry, Chippy088, if I offended you. I could perceive quite clearly the anger in your writing. I did not mean to offend you. I sincerely thank you for your contribution. Again, I am deeply sorry. Secondly, thank you, Carlosdl, for providing me with the article. It is quite long and detailed, and will take me a while to read, absorb, and understand it. I am hoping that after I would have read it, we (all of us) can continue our discussion.
    90 pointsBadges:
    report
  • Whabby
    I have read the artlcle, Carlosdl. Floating Point Arithmetic is a very complex subject to understand. I even consulted several websites on the subject, and the information is still beyond my grasp. However, I learned that Excel uses the popular standard of the design of computing systems in building the programming for spreadsheets. I am very frustrated still, because that means what I thought I could depend on in Excel is no longer dependable. At least what I would like to know from my spreadsheet is 1 + 1 = 2. It looks like I can't even depend on that!!! I am most grateful to you, Carlosdl, for supplying the article, and shedding a bright light on the subject of Floating Point Arithmetic.
    90 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following