Round Value to the Nearest Quarter in Excel

Sometimes as we work with Excel we may need to round values to the nearest quarter.

This tutorial shows you 4 methods of how to round numbers to the nearest quarter in Excel.

Method #1: Apply the MROUND Function

We can use the MROUND function to round a value to the nearest quarter in Excel.

The MROUND function returns a number rounded to the specified multiple. The function takes two required arguments, the number you want to round and the multiple to which you want to round the value.

We will use the following dataset to show how the MROUND function can be applied in rounding a value to the nearest quarter.

The values in column A will be rounded to the nearest quarter and displayed in column B.

We use the below steps:

  1. Select cell B2 and type in the following formula:
  1. Click the Enter button on the formula bar to enter the formula.
  1. Double-click or drag down the fill handle to copy the formula down the column.

The values rounded to the nearest quarter are displayed in column B:

Table

Description automatically generated

Explanation of the formula

The syntax of the MROUND function is as follows:

MROUND(number, multiple)

  • Number This argument is required. It is the number to round.
  • Multiple This argument is required. It is the multiple to which you want to round the Number.

The value in cell A2 is the number and the multiple is 0.25 (a quarter of a number).

Method #2: Use the ROUND Function

We can apply the ROUND function to round values to the nearest quarter in Excel.

The ROUND function rounds a number to a specified number of digits.

We will use the following dataset to show how the FLOOR.MATH function can be used in rounding a value down to the nearest quarter.

Table

Description automatically generated

The values in column A will be rounded to the nearest quarter and displayed in column B.

We use the following steps:

  1. Select cell B2 and type in the formula below:
Table

Description automatically generated
  1. Click the Enter button on the formula bar to enter the formula.
  1. Double-click or drag down the fill handle to copy the formula down the column.

The values that have been rounded to the nearest quarter are shown in column B.

Table

Description automatically generated

Explanation of the formula

The ROUND function has the following syntax:

ROUND(number, num_digits)

  • Number This is a required argument. It is the value you want to round. In this case, the Number is the value in cell A2 multiplied by 4.
  • Num_digits This is a required argument. It is the number of digits to which you want to round the Number. In this case, it is 0 (zero).
  • =ROUND(A2*4,0)/4 The value returned by the ROUND function is divided by 4 to get the value rounded to the nearest quarter.

Method #3: Use the CEILING.MATH Function

The CEILING.MATH function can be used to round a value to the nearest quarter in Excel.

The CEILING.MATH function rounds a value up, to the nearest integer or the nearest multiple of significance.

We will use the following dataset to show how the CEILING.MATH function can be used in rounding a value up to the nearest quarter.

The values in column A will be rounded to the nearest quarter and displayed in column B.

We use the following steps:

  1. Select cell B2 and type in the below formula:
Table

Description automatically generated
  1. Click the Enter button on the formula bar to enter the formula.
  1. Drag down or double-click the fill handle to copy the formula down the column.

The numbers rounded to the nearest quarter are displayed in column B.

Table

Description automatically generated

Explanation of the formula

The syntax of the CEILING.MATH function is as follows:

CEILING.MATH(number, [significance], [mode])

  • Number This argument is required. It is the value to be rounded up. In this case, the value to be rounded up is in cell A2.
  • Significance This argument is optional. It is the multiple to which the Number is to be rounded. In this case, the multiple is 0.25, which is a quarter of a number.
  • Mode This argument is optional. It only applies to negative numbers where it determines whether the Number is to be rounded away from or toward zero. In this case, the argument was omitted because the values in the example are not negative.

Method #4: Use the FLOOR.MATH function

If we are interested in rounding the values down to the nearest quarter then the FLOOR.MATH function is the one to use.

The FLOOR.MATH function rounds a number down, to the nearest integer or the nearest multiple of significance.

We will use the following dataset to show how the FLOOR.MATH function can be used in rounding a value down to the nearest quarter.

The values in column A will be rounded down to the nearest quarter and displayed in column B.

We use the below steps:

  1. Select cell B2 and type in the following formula:
Table

Description automatically generated
  1. Click the Enter button on the formula bar to enter the formula.
  1. Drag down or double-click the fill handle to copy the formula down the column.

The values that have been rounded down to the nearest quarter are displayed in column B.

Table

Description automatically generated

Explanation of the formula

The syntax of the FLOOR.MATH function is as follows:

FLOOR.MATH(number, [significance], [mode])

  • Number This argument is required. It is the value to be rounded down. In this case, the value to be rounded down is in cell A2.
  • Significance This argument is optional. It is the multiple you want to round the number down to. In this case, the multiple is 0.25, which is a quarter of a number.
  • Mode This argument is optional. It only applies to negative numbers where it determines whether the Number is to be rounded away from or toward zero. In this case, the argument was omitted because the values in the example are not negative.

Conclusion

This tutorial showed techniques for rounding a value to the nearest quarter in Excel. The methods involve using MROUND, CEILING.MATH, FLOOR.MATH, and ROUND functions. We hope you found the tutorial helpful.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.