Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Roundup

  1. #1

    Roundup



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to write a formula where it rounds to the nearest whole percentage, i.e. 6.88% to 7%, or 93.12% to 93%. I then want to use that cell and the rounded percentage in future calculations. How do I tell excel to use the rounded percentage from there. HELP!

  2. #2
    You can use the Excel =round() function. It takes two arguments, one is the data you want to round and the second the number of decimal places. Since you're working with percentages you'll need two decimal places to round it off to a single percentage.

    Ie if your number is in cell A1 then you would use:

    =round(A1,2)

    Jesse

  3. #3
    Thank you, I think I got that part. Now what if I need to first take the sum of A1/A2, i.e. =SUM(A1/A2) and round the answer to the nearest whole number?

  4. #4
    Assuming that =SUM(A1/A2) returns a percentage your formula would be:

    =ROUND(SUM(A1/A2),2)

    Jesse

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    =ROUND(A1/A2,0)

    But... I would ask yourself if you really want to round each calculation as you go through, or only the final number. By roundind each step in the process you may find that your final number isn't as mathematically accurate... depending on your purpose of course.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Good point Ken, unless you're rounding for significant digits it should be the very last stop in the calculation. Even at that point it would be better to use number formatting to display the significance you require.

    Jesse

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Ha! Didn't realize that you posted as I was writing my reply.

    Bondiee, for what it's worth, I wrote the exact same formula as Jesse to start with. I removed the SUM though, as it wasn't necessary. (The computation would go in the order of operations. i.e. it would divide the two numbers, giving you one number which was then summed up.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Doh! I didn't even notice the sum.

    Jesse

  9. #9
    Thanks guys! That is all very helpful. You also touched on the other problem that I was having. Once I round that percentage, I need to then multiply another field by the rounded percentage. But Excel wants to round by the true calculation and not the rounded number, how do I tell it to use the rounded number in future calculations??

  10. #10
    If you reference the calculation that uses =round() in another calculation it will use the rounded amount.

    Jesse

Page 1 of 2 1 2 LastLast

Posting Permissions

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