Rounding sums to two decimal places in Excel

I had an interesting problem. I created an Excel spreadsheet assignment marking rubric with many items and categories, which self-added and worked out the marking splits (just by putting a character into the appropriate grade column), then automatically summed the section totals and overall total. However, the summing was untidy, and to my mind, looked unprofessional going back to the students. For example, I would end up with sums of "2.37", which I would ideally have liked to have automatically rounded down to 2.35. I wanted each sub-total cell to round up or down to the closest 0.05 without human interference.

I already had a formula and range that I wanted to sum (the sum of a series of marking splits, plus a manual adjustment figure, if required):

=SUM(N6:N14)+L6

What I found was an Excel function called MROUND.This is designed to work with time or currency to create 5 second roundings for recording logs (using the "0:05" operator), or or 5 cent roundings for pricing ("0.05"). You can also round to ten seconds or cents using "0:10" or "0.10".

All I then had to do was to nest my sum into the MROUND formula, which looked like this:

=MROUND((SUM(N6:N14)+L6), "0.05")

And I was home and hosed! No more 2.37 sums :-)


Sam

References:

• French, T. (9 August 2017). Combining the ROUND and SUM Functions in Excel. Retrieved 7 September 2017 from https://www.thoughtco.com/nesting-the-round-and-sum-functions-3123754
• Cheusheva, S. (6 August 2015). How to round numbers in Excel - ROUND, ROUNDUP, ROUNDDOWN and other functions. Retrieved 7 September 2017 from https://www.ablebits.com/office-addins-blog/2015/08/06/excel-round-functions/