+ Reply to Thread
Results 1 to 17 of 17

Need to create formula wich will reduce random rounding. I need to explain further

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Post Need to create formula wich will reduce random rounding. I need to explain further

    My spreadsheet has several lines each of which has a mixture of data and formulas. At the end of the line going across, is the final formula. However, most times, the result is rounded up or down when compared to the same information entered in an accounting programme. I am using SUM formulas at that moment.
    Last edited by Karen@TSCL; 07-03-2012 at 03:22 PM. Reason: This has been solved thanks to ChemistB.

  2. #2
    Registered User
    Join Date
    06-14-2012
    Location
    Cambridge, UK
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Sorry, what is your question?

    Do the =ROUNDUP( or =ROUNDDOWN( or = MROUND( formulae help?
    <--- Please click the star to say thank you

  3. #3
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    From what I have read about ROUNDUP, etc, these are for whole numbers. My spreadsheet figures show 2 decimal places. I tried EVEN but that also does whole numbers.

  4. #4
    Registered User
    Join Date
    06-25-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Hi, are you need to reduce the decimal places or trying to round off.
    Could you explain your problem clearly.

  5. #5
    Registered User
    Join Date
    12-15-2011
    Location
    Dallas texas
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    try
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    MMctague, that format is new to me. How do I add it or amend this formula to include your suggestion? Here is a sample of the formula =SUM(G1:M1)*F1+N1+O1+SUM((G2:M2)*F2+N2+O2. Each result comes from a multiple of 2 lines. Hence it is either 2 lines or 4 or 6, etc. The sample above picks up 2 lines

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    From what I have read about ROUNDUP, etc, these are for whole numbers.
    No, ROUNDUP works just as ROUND does except ROUND uses 0.5 as a breakpoint. For ROUNDUP, for example, =ROUNDUP(A1,2) if A1 contains 2.25001.... to 2.259999... it will round to 2.26
    MROUND can be used to set specific endpoints =MROUND(A1,0.05) rounds the value in A1 to the nearest 0.05.
    Hope that helps.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    However, most times, the result is rounded up or down when compared to the same information entered in an accounting programme.
    Most likely, the accounting program is rounding or the inputs are being rounded. Increase the number of digits the results are displayed as to see extra digits (up to 15 places). Excel uses all digits whether you have it formatted to display or not. That would be why you want to ROUND.

  9. #9
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Chemist, based on the sample formula I provided, can you give me an example of how I would incorporate ROUNDUP to get the desired result?

  10. #10
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Banuchander, please read the reply I sent to MMctague where I provided a sample formula. What keeps happening is that result I see in Excel sometimes differs by a cent or two (sometimes more) from what the accounting programme produces. It may seem like a minor difference but when you have to do about 200 of those transactions, it does make a difference.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Depends on where the discrepancy is coming from. If for example, you're showing digits to 1 decimal place and G1:M1 actually equal 2.14, 2.12, 2.23, 2.32, 2.44, 2.43, 2.34, then SUM(G1:M1) will equal 16.0 whereas your accounting program where values are being entered to 1 decimal only (2.1, 2.1, 2.2, 2.3, 2.4, 2.4, 2.3) will give 15.8. ROUNDUP will actually take the value further from your Accounting program. :-/

    Can you upload your spreadsheet (Go Advanced>Manage Attachments) so we can determine that?

  12. #12
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Chemist, I see what you are saying. However, on the spreadsheet, the decimals are formatted to 2 decimal places. It will take me a while to upload the spreadsheet because I will need to make it generic for the sake of privacy. I may not be able to do this until later today or tomorrow.
    Last edited by Karen@TSCL; 06-27-2012 at 03:04 PM. Reason: typo errors

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Another option, Change your spreadsheet to "Precison as displayed"
    Options>Advanced>"When Calculating this workbook" > check "Set Precision as Displayed"
    Did that fix your problem?

  14. #14
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Chemist, I checked the box which is "set precision as displayed" and now see a warning which says "Data will permanently lose accuracy". Can you tell what does that mean overall? How will that affect my figures? In what way will the accuracy be compromised?
    Last edited by Karen@TSCL; 06-27-2012 at 03:38 PM. Reason: added two more questions to the reply

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    If the values are not part of a formula, (i.e. you copied from someplace else a value of 3.12005), you will forever lose that last 005 if you are showing 2 digits. To be safe, save it under a different name first to see if you're happy with the result.

  16. #16
    Registered User
    Join Date
    06-27-2012
    Location
    Grand Cayman, Cayman Islands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    Chemist, I have saved the document as a new file and done the precision thingie. However, I cannot see any difference-no better, no worse. The rounding may be needed in one of the formulas in one of the cells in the ranges. What do you think? I am not familiar with creating formulas using ROUND. May I have some assistance with amending this formula to include ROUND? Here is the formula =SUM(P2*0.05). By the way, I should give you the final formula which is =P2-(Q2+R2+S2+T2+U2+V2).
    Last edited by Karen@TSCL; 06-27-2012 at 04:29 PM. Reason: added another sample formula

  17. #17
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Need to create formula wich will reduce random rounding. I need to explain further

    You would want to round last so it would be

    =ROUND(SUM(P1*0.05),2) assuming you're rounding to 2 decimal places.
    Hope that helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1