Do you need to round numbers to the nearest multiple of 5? This post is going to show you all the methods you can use to round numbers to the nearest multiple of 5 in Google Sheets.

Google Sheets is a fantastic tool that can handle any calculations you require. Rounding numbers to the nearest multiple of an integers is one such task.

Canada recently got rid of their 1-cent coin and the next highest coin available is the 5-cent coin. This means all prices must be rounded to the nearest multiple of 5 cents. For example, 87 cents would be rounded down to 85 cents, but 88 cents would get rounded up to 90 cents.

After reading this post, you’ll be able to quickly and easily round any number to the nearest multiple of 5. Get your copy of the example workbook used in this post and follow along!

Round Numbers to the Nearest Multiple of 5 with the ROUND Function

The ROUND function is the most utilized function when a rounding task is required of users.

Most will use this function to round numbers to a decimal value. It takes a value as an argument and then rounds it to the desired number of decimals.

Rules for rounding numbers with the ROUND function are simple.

• Digits less than five are rounded down.
• Digits equal to 5 or higher are rounded up.

Syntax for the ROUND Function

= ROUND ( value, [places] )

• value specifies the value you want to round.
• places is an optional argument and specifies the number of decimal places to which the value should be rounded. If not specified, the default value is set to zero.

Example

Suppose you have a set of numbers, as shown above, and you want to round them to the nearest multiple of five.

= ROUND ( B3 / 5, 0 ) * 5

Enter the above formula in cell C3 to round the number from cell B3 to the nearest multiple of 5.

Explanation

• Divide the value in cell B3 by 5. For example, 11 is divided by 5 and returns 2.2 as the result.
• The ROUND function is then used to round the decimals to zero places. In this example, the value of 2.2 is rounded down to 2 as a result.
• Then multiply this rounded result by 5 so that the value you get is in the multiple of 5. By doing this, you are actually rounding the original value to the nearest 5. In this example, the result is 10 since 11 is closer to 10 rather than 15.

Drag this formula down across the rows to round all the numbers in the original number to its nearest multiple of 5.

Round Numbers to the Nearest Multiple of 5 with the TEXT Function

If you want to change the appearance of any value, the TEXT function is the best method.

It takes any numerical value as an argument, then converts it into formatted text based on your inputs.

In this method, you can use the TEXT function to round any number to its nearest 5. The TEXT function can be used in a similar way to the ROUND function to round values to zero decimal places.

Syntax for the TEXT Function

= TEXT ( number, format )

• number specifies the value to which you want to apply a number, date, or time format.
• format specifies the format pattern based on which the values provided in the first argument should be formatted.

Example

= TEXT ( B3 / 5, "0" ) * 5

Use the above formula in cell C3 of the sheet to round the numbers to the nearest 5 using the TEXT function. You can then copy and paste the formula down the rows to populate the result.

Explanation

The working of this function is on similar lines to that of the ROUND function.

• You first divide the value in cell B3 by 5 and get a number with decimal formatting.
• The TEXT function then rounds the result by formatting it with zero decimal places. This is the "0" input that specifies the format of the number without decimal places.
• You then multiply the result with 5 to get the rounded output to the nearest multiple of 5.

📝 Note: The TEXT function produces text values but multiplying these by 5 will convert the text back into numbers.

Round Numbers to the Nearest Multiple of 5 with the Conditional IF

You can use a conditional IF statement and round the number to its nearest 5!

You can actually use the IF function to round the numbers to their nearest 5 with the help of other built-in functions such as MOD, FLOOR, and CEILING.

The FLOOR function will always round a number down to the lower multiple, and the CEILING function will always round a number up to the highest multiple.

The IF function can then be used to display either the FLOOR or CEILING result.

=IF(
AND ( MOD ( B3, 5 ) > 0, MOD ( B3, 5 ) < 5/2 ),
FLOOR ( B3, 5 ),
CEILING ( B3,5 )
)

Enter the above formula in cell C3 of the sheet and this will round the value in B3 to the nearest multiple of 5.

Explanation

The MOD function will return the remainder on division, so MOD(B3,5) will return the remainder upon division by 5.

If the remainder is between 0 and 2.5, this means the number is closer to a lower multiple and FLOOR(B3,5) will return this value.

Otherwise, the number is closer to a higher multiple and CEILING(B3,5) will return this value.

Round Numbers to the Nearest Multiple of 5 with the MROUND Function

The previous methods were specific uses of standard functions to round to multiple values.

However, there is a function where you just specify the cell to round and the multiple you want to round the values toward!

The MROUND function can take a number and round any multiple!

Syntax for the MROUND Function

= MROUND ( value, factor )

• value specifies the number you want to round.
• factor specifies the multiple to which you wish to round the value.

The purpose of the MROUND function is to use it when you want to round a given number to the nearest multiple of an integer value.

Example

= MROUND ( B3, 5 )

Enter the above formula in cell C3 to round your values to the nearest multiple integers.

Explanation

• Use cell B3 as a first argument that specifies the value you want to round.
• Then, for the second argument, use 5 as a factor so that the function can round the value in cell B3 to the nearest integer in a multiple of 5 (factor).

💡 Tip: MROUND is the most straightforward way to round numbers to the nearest multiple.

Round Numbers to the Nearest Multiple of 5 with the Apps Script

Apps Scripts in Google Sheets are the best way to automate repetitive tasks with code.

You can check out the beginner’s guide to app scripts in Google Sheets to discover the basics. This will show you how to add code on your own.

To launch the Apps Script, Go to the Extensions menu and select the Apps Script option. Click on it to launch the apps script editor in a new browser tab.

function Round5() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var selectedRange = ss.getActiveRange()
  var selectedValues = selectedRange.getValues()
  var selectedColumns = selectedRange.getWidth()
  var selectedRows = selectedRange.getHeight()

  for (i = 0; i < selectedRows; i++) {
    for (j = 0; j < selectedColumns; j++) {
      selectedRange.getCell(i + 1, j + 1).setValue(Math.round(selectedValues[i][j] / 5, 0) * 5);
    }
  }
}

Copy and paste the code above into the script editor that opens up in a new browser and then press the Save button.

Explanation

• The first line of code allows you to define a new function named Round5().
• The second line of code accesses the current sheet in the active workbook with the SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() method.
• The selected range from the sheet is retrieved with the help of the getActiveRange() method and stored in a variable named selectedRange.
• The number of rows and columns in the active range is determined by using the getHeight() & getWidth() methods on the selectedRange.
• A for statement loops through each cell in the selected range and rounds the values to the nearest multiple of 5 with the Math.round() function.

Click on the Run button in the script editor to execute this script. If you are using it for the first time under your project, you will need to authorize it through your Google Account.

Once you run the script and it is executed, you will see that the values are now rounded to the nearest multiple of 5 inside column B.

⚠️ Warning: This script will overwrite your original numbers with the rounded numbers. Make sure you create a backup copy of the data before you run the script!

Conclusions

These are a few ways of rounding a given number to an integer nearest to the multiple of 5.

The ROUND function is the most familiar way to round numbers in Google Sheets and it can be used to round the number to the nearest 5. The TEXT function can also be used in a similar fashion

The IF function when combined with the MOD, FLOOR, and CEILING, can round a number to the nearest multiple of 5. But the implementation is a bit complex.

The MROUND function is specifically designed for the task of rounding numbers to specific multiples. This is the easiest way to round values to the nearest multiple of 5 and you should consider using this method above all other formula methods.

Finally, the Apps Script allows you to automate the process of rounding. A script can be used to overwrite your data with the rounded results. This might be a cleaner method rather than rounding with a formula.

Were you aware of these methods of rounding a number to the closest multiple of 5? If you know any other methods to get this done, let me know in the comments!