Tag Archives: round

XL FORMULA FOCUS: ROUND, ROUNDUP, ROUNDDOWN, MROUND (an introduction to Excel rounding functions)

Excel Formulas to Round Numbers
ROUND, ROUNDUP, ROUNDDOWN, MROUND – Excel Formulas to Round Numbers

Here is a quick introduction to four straight-forward rounding functions in Excel. We’ll look at each formula in the picture above and how they help you to round numbers.

[showmyads]

ROUND(number, num_digits)

This is probably what you think of instinctively when you hear the word “rounding”, and so this might be the formula you use most often. In the example above we’re rounding to two decimal places, so if the third decimal place is 5 or above we round the second decimal place up, and if the third decimal place is below 5 we round the second decimal place down. If num_digits is 0 then it rounds to the nearest integer, and if num_digits is negative the number is rounded to the left of the decimal point.

ROUNDUP(number, num_digits)

You’ll start to see a theme emerging here – all of these rounding functions follow the same syntax (number, num-digits). So if you understand ROUND then ROUNDUP should be fairly intuitive for you. But just to spell it out, in the example above we’re rounding to two decimal places, so if the third decimal place is above 0 it rounds the second decimal place up, otherwise it stays the same.

ROUNDDOWN(number, num_digits)

Again the same format, only this time we’re rounding down. In the example above we’re rounding to two decimal places, so regardless of what the third decimal place is, we’re chopping it off and the second decimal place stays as it is.

MROUND(number, num_digits)

[showmyads]

MROUND, or Multiple Round, rounds your number to the nearest multiple of your choice (guess what – same syntax!). So above we decide to round to the nearest 5, and it uses “standard” rounding and returns the figure up or down to the nearest multiple of 5.

That was your whistle-stop tour of Excel’s most straightforward rounding functions. Thanks for listening!

XL FORMULA FOCUS: Need to calculate length of service or age? Use YEARFRAC

Length of service is used all the time in HR – for example long service awards or calculating redundancy payments. Those of you fortunate enough to have a solid HR system in place will hopefully have some automation to keep you aware of upcoming key dates, but for others Excel can be the next best thing.

We will be using the YEARFRAC function:

YEARFRAC(start_date, end_date, [basis])

 

Let’s do an example…

1. In cell A1, enter your date of birth (for arguments sake use the format dd/mm/yyyy).

2. In cell B1, enter  =TODAY()  . This will return today’s date, which will update automatically as time goes on.

3. In cell C1, enter  =YEARFRAC(A1,B1,1)  .

 

The number one at the end of the formula is what is referred to above as the ‘basis’. It tells Excel on what basis to calculate the difference between start_date and end_date. Personally I’ve always used 1, which is described as Actual, and it has always been accurate and fit for purpose. If you’re unsure, while working on the YEARFRAC formula hit the fx button next to the formula bar, and then select ‘help on this function’. It will give you a bit more information about the YEARFRAC function and detail the various basis options.

 

So, cell C1 should now show you the number of years between your start date and end date (in this case how old you are today). Depending on how you format this cell (and assuming its not your birthday – if it is happy birthday!) you will probably have a number of decimal places shown. This is fine if you want a very precise figure, but in many practical applications you will want completed years (e.g. I am 30 years old, not 30.153663 years old). So for this we’re going to use the INT function (short for Integer).

 

4. In cell D1, enter INT(C1)  .

 

You will now have your age shown as an integer.

If you wanted to you could condense this into fewer cells.

 

5. Select cells B1, C1 and D1 and press delete.

6. In cell B1, enter this:

=INT(YEARFRAC(A1,TODAY(),1))

You can now use this formula repeatedly, changing the contents of cell A1 to any date that you want.

If you’ve made it this far Well Done! You’ve used 3 different functions in one formula to give a practical output.

If you liked that, here’s a post about EDATE, a really straightforward date function that I use to calculate the of probation periods or notice periods.