This article applies to all versions of Microsoft Excel For Windows®, unless stated otherwise.

Author(s): Bob Phillips
© Copyright, 2004, Bob Phillips

Date Written: 1st September 2004
Last Updated: 5th September 2004

Article Summary

This paper discusses rounding in Excel. Rounding would normally be applied to a number, but there is often a requirement to round time, or even a date. In addition, you may need to apply rounding in worksheets, or within a VBA macro or function.

Article Contents

All of these are discussed in this document, under the following topics:

What Is Rounding?
Round To n Decimal Places
Round Up/Down
Rounding Numbers Summary
Related Functions
Rounding Fractions/Multiples
Rounding Time
Rounding Days
Rounding In VBA
Acknowledgments & References

This article assumes familiarity with the following:

What Is Rounding?

The following is a dictionary definition of rounding

... a miscalculation that results from rounding off numbers to a convenient number of decimals ...

In the context of this discussion, we are taking a wider view of rounding within Excel, and so it means to calculate a value to a defined level of precision, what I call the rounding factor, from a starting value, or given value. The most obvious form of rounding is to calculate a value rounded to the nearest whole number, or integer. But it could equally be rounded to the nearest half, tenth, two, ten, .637, hour, Monday, etc.

Rounding can be either up or down, dependent upon where the given value is in relation to the rounding factor. The given number will necessarily be partway between largest number that is an exact multiple of the rounding factor less than the given number, the lower boundary, and a number that is an exact multiple of the rounding factor greater than the given number, the upper boundary.

If the given value is equal to or greater than the lower boundary + half of the rounding factor, this value rounds to the upper boundary. If it is less, it rounds to the lower boundary. In other words, if it is greater than or equal to the halfway point between the lower boundary and upper boundary is rounded up.

In certain circumstances, the default rounding needs to be over-ridden, to round a value up or down, regardless of the value.

An example would best clarify this. If we are rounding a given number, 1.3 say, by a rounding factor of 0.2, or one fifth. Then the given number will round to either 1.2 (6 times .2) or 1.4 (7 times .2).

For a rounding factor of 0.2, the halfway point is 0.1, for 17 it is 8.5, for 1.37625 ... well I have no idea, but I am sure that you get the point.

Rounding up or down is more straight-forward, it increase or decreases the given value to the upper boundary or the lower boundary regardless of where the given value is, relative to these boundaries.

Round To n Decimal Places

The simplest form of rounding is to round to a pre-defined number of decimal places. The worksheet function required is ROUND. The syntax for ROUND is
ROUND(number,num_digits)
number is the number to be rounded, and can be a value or a reference to a cell containing a value or a formula that resolves to a value,
num_digits is the precision to be rounded to, which can also be a value or a reference to a cell containing a value or a formula that resolves to a value.

Note that precision is not the same thing as the rounding factor. A precision of 0 decimal places is a rounding factor of 1, a precision of 2 is a rounding factor of 0.01.

As an example, to round a number, stored in cell A1, to the nearest whole number, we would use

=ROUND(A1,0)

In this example, a value of 1.24 in A1 would be rounded to 1., 1.67 would be rounded to 2

Similarly, to round to 1 decimal place, we use a value precision of 1 .

=ROUND(A1,1)

In this example, a value of 1.24 in A1 would be rounded to 1.2, 1.27 would be rounded to 1.3.

If you need to round to the nearest 10, nearest 100, etc. we use a negative precision value.

So, to round to the nearest 1000, we use a value of -3 as the precision.

=ROUND(A1,-3)

In this example, a value of 1,756.24 in A1 would be rounded to 2000.

Round Up/Down

In certain circumstances, there may be a need to round the result up to the nearest required value, up or down. For example, rounding 1.24 to the nearest single decimal place returns 1.2, but rounding up to the nearest single decimal place returns 1.3.

In this case, we use the ROUNDUP and ROUNDDOWN functions, which has the same syntax as ROUND, and the same criteria apply.

However, ROUNDUP and ROUNDDOWN are supplemented by two similar functions, CEILING and FLOOR.

The syntax of CEILING is
CEILING(number,significance)
where
significance is the multiple to be rounded to.

CEILING returns the number rounded up to the nearest multiple of significance.

Taking the example that we used in ROUNDUP above, to round 1.24 up to the nearest .1, we use

=CEILING(A1,0.1)

Similarly, to round this value down to the nearest 0.1, we would use

=FLOOR(A1,0.1)

Whilst both CEILING and FLOOR return the same results as ROUNDUP and ROUNDDOWN, they have the advantage of specifying the value to round up or down to in the function call, which aids readability and maintenance. It becomes more useful when rounding fractions up or down, as described below.

To summarise, we now have the following formulae in our formula toolbox

ROUNDUP(value,num_digits)
ROUNDDOWN(value,num_digits)
CEILING(value,significance)
FLOOR(value,significance)

Rounding Fractions/Multiples

We have seen how to round decimals above, so how would we round to a fraction or an integer other than 1, say to the nearest half, the nearest quarter, or the nearest 5?

For fractions, the trick here is to first multiply the number by the inverse of the fraction, round to zero decimal places, and then divide by the original multiplier.

Let's walk through an example. We will round 1.34 to the nearest quarter. These are the steps we follow:

Mirroring this in functions, assuming the value to be rounded is in A1, we get:

The generic formula is then

=ROUND(A1*n,0)/n

where A1 is the cell holding the value, and n is the inverse of the fraction (quarter is 4, half is 2, third is 3, etc.).

For multipliers, a similar principle is applied, except that we divide by the multiplier before rounding, and then multiply the result by that multiplier, i.e.

=ROUND(A1/m,0)*m

Clearly, it would be useful if these two forms could be amalgamated, and they can. If we don't invert the fraction, but use that fractional value directly. then we can just use the second form, namely

=ROUND(A1/n,0)*n

Another, not so common function that can be used is MROUND. MROUND is part of the 'Analysis Toolpak', so this add-in will need to be installed. MROUND rounds to the nearest multiplier. As an example

=MROUND(A1,3)

rounds the value in A1 to the nearest 3. This can also be used to round fractions

=MROUND(A1,0.25)

Rounding up or down is done in a similar way tol standard rounding. So, rounding 1.34 (in A1) down to the nearest quarter is

=ROUNDDOWN(A1/0.25,0)*0.25

and rounding up to the nearest quarter is

=ROUNDUP(A1/0.25,0)*0.25

But here we can also use CEILING and FLOOR in a more intuitive way, namely

=FLOOR(A1,0.25)

and

=CEILING(A1,0.25)

To summarise, we have now developed the following formulae

ROUND(A1*n,0)/n
ROUNDUP(A1*n,0)
ROUNDUDOWN(A1*n,0)
CEILING(A1,n)
FLOOR(A1,n)

where n is the rounding factor.

It should be clear at this point that the formulae provided in the section Round To n Decimal Places and Round Up/Down are just special cases of the formulae here, where n has the value 1.

Rounding Numbers Summary

The previous paragraphs show a number of ways of rounding a value, to a defined precision. The following table shows the results of the various formulae presented above on three different value, 1.3, 1.5 and 1.67, to a number of levels of rounding precision, that of value of 1, 0.2 (or 1/5), 0.25 (1/4), 0.5 (1/2) and 2.


Fig. 1.

Related Functions

Excel offers other functions which are related to the rounding functions discussed above:

TRUNC returns the whole number of the processed value by truncating the decimal part. INT returns the next smallest whole number. For positive numbers both functions return the same value. Their results differ for negative numbers only:

=TRUNC(1.3) = 1
=INT(1.3) = 1
=TRUNC(-1.3) = -1
=INT(-1.3) = -2

TRUNC(number) returns the same result as ROUNDDOWN(number,0).

EVEN and ODD rounds the value to the next even and odd number respectively:

=EVEN(1.3) = 2
=ODD(1.3) = 3

=EVEN(-1.3) = -2
=ODD(-1.3) = -3

Note: =EVEN(0) returns zero as result (that is Excel treats zero as an even number)

Rounding Time

The same principles can be applied to rounding time as to rounding any other number, but it will help to understand first how time is stored in Excel.

Excel stores times as a fraction of 1 day. So 12 hours is stored as 0.5, 6 hours as 0.25, 8 hours as .3333 and so on. When you see time as say 08:00, what you are seeing is simply a special formatting of the fractional value 0.3333 (to show this, enter 08:00 in A1, in A2 enter =A1 and format it as 'General', and you will see the value 0.3333).

Because of the way that time is stored, the rounding is not so intuitive. For instance, suppose that A1 has the value 08:21, then to round this to the nearest hour would NOT be

=ROUND(A1,0)

as that returns a value of 0. Why? Well, as A1 a time value mof 8 hours and 21 minutes, and time is a fraction of 1 day, the underlying value of A1 is 0.333. Rounding this to zero decimal places yields 0.

The trick here is to apply the same principles to rounding time as we applied to Rounding Fractions above. In this instance, one hour is one 24th of a day, so to round to the nearest hour, we multiply the time by 24, round to zero decimal places, then divide by 24 to revert to a faction of 1 day. Taking our example of A1 with the value 08:21, we would use

=ROUND(A1*24,0)/24

Breaking this down, we have:

Formatted as time, we get our round time value of 08:00.

Similarly to round to the nearest half hour, we use

=ROUND(A1*24*2,0)/24*2 or

=ROUND(A1*48,0)/48

For the nearest quarter-hour

=ROUND(A1*96,0)/96

And for the nearest 5 minutes

=ROUND(A1*288,0)/288.

The same principle applies to rounding up. To round up to the nearest hour, we would use

=ROUNDUP(A1*24,0)/24

Yet again, in place or ROUNDUP, we can use CEILING, which makes the answer a little more intuitive

=CEILING(A1,1/24)

Then, we can also use ROUNDOWN and FLOOR, such as

=ROUNDDOWN(A1*24,0)/24

and

=FLOOR(A1,1/24)

For clarity and maintenance, it may be better to express the following formula

=ROUND(A1*288,0)/288

as

=ROUND(A1*(5*24*60),0)/(5*24*60)

which better illustrates what is happening.

As an added readability and maintenance aide, it is a good idea to create a workbook name for the constant 'number of minutes per day'. To do this, select the menu Insert>Name>Define, and enter _MinsPerDay in the 'Name' box, and the value =24*60 in the 'RefersTo' box - see Fig. 2 below


Fig. 2.

We can then use this name in our formulae. An example would be

=ROUND(A1*(5*_MinsPerDay),0)/(5*_MinsPerDay)

As generic formulae, we then have

=ROUND(A1*( _MinsPerDay/ mins_to_round),0)/(_MinsPerDay/ mins_to_round)

=ROUNDUP(A1*(_MinsPerDay/mins_to_round),0)/(_MinsPerDay/ mins_to_round)

=ROUNDDOWN(A1*(*_MinsPerDay/mins_to_round),0)/(_MinsPerDay/ mins_to_round)

=CEILING(A1, mins_to_round/(_MinsPerDay),0)

=FLOOR(A1, mins_to_round/(_MinsPerDay),0)

Rounding Days

Rounding dates is really a bit of a misnomer. What is happening is finding the nominated day nearest to a given date. As with rounding, sometimes it goes up (forward by days), sometimes down (back by days).

Before we start on this solution, a short explanation. This solution uses the WEEKDAY function to get a day number for a date. WEEKDAY can take various values as the second argument, but in our solution(s) here, we will always take the default. In this form WEEKDAY of a Sunday date returns 1, Monday returns 2, etc., through to Saturday which returns 6.

To get to the nearest nominated day, the given date has to be offset by a number of days, positive if the date is forward of the given date, negative if the day is back from the given date. The rule is:

The following table shows the number of days to be added or subtracted for each 'Weekday of Date'/'Nearest Day' combination.

  Nearest Day
Sun Mon Tue Wed Thu Fri Sat
Weekday Of Date Sun 0 1 2 3 (3) (2) (1)
Mon (1) 0 1 2 3 (3) (2)
Tue (2) (1) 0 1 2 3 (3)
Wed (3) (2) (1) 0 1 2 3
Thu 3 (3) (2) (1) 0 1 2
Fri 2 3 (3) (2) (1) 0 1
Sat 1 2 3 (3) (2) (1) 0
Fig. 3.

The numbers in brackets are negative numbers, where a previous date is required.

The relevant value from the matrix needs to be added to the given date to get the date of the nearest nominated day.

To achieve this, we could simply create a lookup table for every combination of weekday of the given date and the nearest day number, and use the result from that lookup to add to (or subtract from) the given date. However, there are many redundant values in the matrix (only 7 unique values in a 49 combination matrix), so this could be quite wasteful.

Instead, what I have done is to create lists of the six non-zero offsets, and use these in a CHOOSE function, using the difference of the weekday of the date and the day number to use as an index. As the weekday of the date will sometimes be less than, sometimes be equal, and sometimes be greater than the nearest day number, we need three conditions. These are:

Putting this all together, assuming that the given date is in A1, and the day number of the target nearest day is in B1, we have

=IF(WEEKDAY(A1)=B1,A1,IF(WEEKDAY(A1)>B1,A1+CHOOSE(WEEKDAY(A1)-B-1,-2,-3,3,2,1),A1+CHOOSE(B1- WEEKDAY(A1),1,2,3,-3,-2,1)))

Using this formula, with a date 30 Aug 2004 in A1, and a day number of 3 (for Tuesday), we get 31 Aug 2004, which is the Tuesday after the given date. But, the nearest Saturday (day number of 7) gives 28 Aug 2004, the previous Saturday. This latter result is because the next Saturday is more than 3 days forward of the given date, so the previous Saturday is used.

Decomposing this formula, we have

This formula can be simplified as one list -1,-2,-3,3,2,1 is the negative of the other, 1,2,3,-3,-2,-1. We can use the absolute value of the difference between the weekday of the given date and the target day, as the index to the CHOOSE function, which, if multipled by 1 when the difference is positive, -1 when it is negative, will handle either condition.

The case for the difference between the weekday of the given date and the target day being zero also needs to be managed, This all gives rise to the formula
=IF(WEEKDAY(A1)=B1,A1,A1+CHOOSE(ABS(WEEKDAY(A1)-B1),-1,-2,-3,3,2,1)*SIGN(WEEKDAY(A1)-B1))

Or, by catering for the equal condition in the CHOOSE (by adding 1 to the difference calculation, we can simplify it more to
=A1+CHOOSE(ABS(WEEKDAY(A1)-B1)+1,0,-1,-2,-3,3,2,1)*SIGN(WEEKDAY(A1)-B1)

Alternatively, we can use the following, more elegant but less obvious, formula

=A1+B1-WEEKDAY(A1)-7*(ABS(WEEKDAY(A1)-B1)>3)*SIGN(WEEKDAY(A1)-B1)

In this formula we first calculate the date of the next target day after the given date, not the nearest which is sometimes earlier but always the next, with A1+B1-WEEKDAY(A1)

To this date we add or subtract 7 days, depending upon whether the difference in the weekday of the given date and the target day is >3, ABS(WEEKDAY(A1)-B1))>3. The determination of whether to add or subtract is made by testing the sign of the difference in the weekday of the given date and the target day SIGN(WEEKDAY(A1)-B1). These two partial formulas are multiplied, and then by 7 to calculate days add/subtract from the previously calculated date, and added to that date to give the final date.

Or even shorter ...

=A1+B1-WEEKDAY(A1)-7*TRUNC((WEEKDAY(A1)-B1)/4)

... but I will leave you to work that one out.

Rounding In VBA

VBA only has a single rounding function, namely Round. This function works in a similar way to the worksheet ROUND function. As an example, to round a value to the nearest whole number, we can use VBA code such as
Round(1.2,0)
which returns a value of 1 in this instance.

The Round function was not introduced into VBA until Excel 2000, so if you have an earlier version, you will need to use something else. And this is how.

We can use the Int function to round a value, but as it is effectively rounding down to the integer, we need a little trick. By adding 0.5 to the value, and then applying the Int function, this effectively rounds the value. For instance
Int(1.2 + 0.5)
returns a value of 1, whilst
Int(1.6 + 0.5)
returns a value of 2.

To make this more generic, to be able to round to a multiple or a fraction, we would use the formula
Int((value + (0.5 * rounding_factor)) / rounding_factor) * rounding_factor

The function call
Int(value + 0.5)
can be seen as just a special case of
Int((value + (0.5 * rounding_factor)) / rounding_factor) * rounding_factor
where rounding factor has a value of 1.

A similar VBA statement for rounding up or down is a lot more complex, but there is a simpler way, by rounding the value as described above, and then comparing the original value to the rounded value. For rounding down, if this is greater, then the rounding factor is subtracted, whilst for rounding up, if this is less, then the rounding factor is added.

For rounding up, we have
thisVal = Int((start_value + (0.5 * rounding_factor)) / rounding_factor) * rounding_factor
If thisVal < start_value Then thisVal = thisVal + rounding_factor

For rounding down, we have
thisVal = Int((start_value + (0.5 * rounding_factor)) / rounding_factor) * rounding_factor
If thisVal > start_value Then thisVal = thisVal - rounding_factor

Amalgamating the round code, the round up and the round down code, we can create a composite function that handles all cases.

Function fzRound(Val As Double, RF As Double, Optional Direction As String = "")
Dim thisVal As Double

    thisVal = Int((Val + (0.5 * RF)) / RF) * RF
    If LCase(Direction) = "up" Then
        If Val > thisVal Then
            thisVal = thisVal + RF
        End If
    ElseIf LCase(Direction) = "down" Then
        If Val < thisVal Then
            thisVal = thisVal - RF
        End If
    End If
    fzRound = thisVal
End Function

A few examples of this function are given here

In addition, you also have access to the worksheet functions, such as CEILING
Application.CEILING(1.2, 0.25) returns 1.25.

The exception to how to use the worksheet functions is the MROUND function. Because this is part of the 'Analysis Toolpak', we cannot use it as a worksheet function. We have two choices, either set a reference to the 'Analysis Toolpak' library and call the function as a standard VBA function, ATPVBAEN.XLA, or use the Application.Run method,
Application.Run("ATPVBAEN.XLA!MROUND", 1.9, 3)
which rounds 1.9 to the nearest 3, returning 3.

Acknowledgments & References

Dick Kusleika has a short article on rounding times on his Daily Dose of Excel page. I borrowed the idea of using a workbook name for the time value from there.

Frank Kabel contributed the section on Related Functions, and the final formula for rounding days (which also inspired the second formula from its use of the SIGN worksheet function).

Chip Pearson has an article on Rounding Errors In Excel at his site.

Downloads

This page can be downloaded as a zipped Word document here (80Kb)