This guide covers everything you need to know about the Google Sheets ROUND function, including its definition, syntax, use cases, and how to use it.
What is the ROUND Function? How Does It Work?
The ROUND function in Google Sheets returns a number rounded to a specific number of decimal places. The ROUND function works by considering the most significant digit to the right of the decimal place to which you’re rounding. If this digit is 5 or higher, the number is rounded up. If it’s lower than 5, the number is rounded down. This rule applies regardless of whether the number is positive or negative.
The purpose of the ROUND function is to decrease the precision of the given number, making it closer to a value that’s more convenient for calculations, data analysis or reporting. For example, if a cell contains the number 99.44 and you want to round it to one decimal place, using the ROUND function would yield 99.4.
In addition to the ROUND function, Google Sheets also offers several other functions for different types of rounding, including ROUNDUP, ROUNDDOWN, MROUND, INT, FLOOR, and CEILING. Each of these functions has a unique way of rounding numbers, allowing for more precise control over your data.
ROUND Syntax
The syntax and arguments for the function are as follows:
ROUND(value, [places])
- value: This is the number that you want to round off. It can be an actual number or a cell reference containing the number you want to round. This argument is required.
- places: This is an optional argument. It represents the number of decimal places to which you want to round the ‘value’. By default, if this argument is not provided, the ROUND function will round your number to the nearest whole number (i.e., 0 decimal places).
Here are some important notes about the syntax and arguments:
- The ROUND function uses standard rounding rules. That means if the digit at the ‘places’ position is 5 or more, the function rounds up the last significant digit. If it’s less than 5, it rounds down.
- If the ‘places’ argument is a negative number, the ROUND function will round the ‘value’ argument to the left of the decimal point. For instance, if ‘places’ is -2, ROUND will round ‘value’ to the nearest hundred.
- The ‘value’ argument can be a formula that results in a number, a number itself, or a reference to a cell containing a numeric value.
- The ‘places’ argument, while optional, must be an integer if provided. If a decimal is provided, it will be truncated to an integer.
- If the ‘value’ is non-numeric, ROUND will return a #VALUE! error.
Examples of How to Use the ROUND Function
Here are some examples that illustrate how to use the ROUND function in Google Sheets:
Example #1
Let’s say you have the number 3.14159 in cell A1 and want to round this number to two decimal places. You would use the ROUND function as follows:
=ROUND(3.14159, 2)
This formula will return the result 3.14. The ROUND function rounds the value to the nearest number with two decimal places.
Example #2
Consider you have a list of prices in column A and want to round all these prices to the nearest whole number. You can use the ROUND function in column B to achieve this. For instance, if you have the price $4.75 in cell A2, you can use the following formula in cell B2:
=ROUND(A2, 0)
This formula will return the result 5. The ROUND function rounds the price in cell A2 to the nearest whole number.
Example #3
Sometimes, you might want to round a number down to the nearest integer. Although this isn’t the primary purpose of the ROUND function, you can achieve this by adding 0.5 to the number before rounding it. For instance, if you have the number 7.8 in cell A5, you can use the following formula in cell B5:
=ROUND(A5 + 0.5)
This formula will return the result 8. The ROUND function rounds the number in cell A5 down to the nearest integer.
Why Is ROUND Not Working? Troubleshooting Common Errors
If you encounter issues while using the ROUND function in Google Sheets, don’t panic. There are a few common errors that can occur, and most of them have simple solutions. Here, we will discuss some of the most common errors, their causes, and how to fix them.
#VALUE! Error
Cause: The #VALUE! error typically occurs when the ROUND function is used with non-numerical values. Google Sheets can’t perform mathematical operations on text, so if your formula includes text, you’ll see this error.
Solution: To fix this, ensure that all input values in your ROUND function are numbers. If you’re referencing a cell, make sure that cell contains a numeric value. If you’re using a formula as an input, ensure that formula is returning a number.
#NUM! Error
Cause: The #NUM! error can happen when you’re trying to round a number to a negative number of digits. This might occur if you’re using a cell reference for the number of digits and that cell contains a negative number.
Solution: The ROUND function can only round to a positive number of digits or zero. Make sure the number of digits you’re rounding to is either zero or a positive number.
#REF! Error
Cause: The #REF! error appears when you’re referencing a cell that doesn’t exist. This could happen if you’ve deleted a row or column that a formula is referencing.
Solution: Double-check your formula to ensure all cell references exist. If you’ve deleted a row or column, you may need to update your formula to reference the correct cells.
#DIV/0! Error
Cause: The #DIV/0! error occurs when a formula is trying to divide by zero. If you’re using a formula as an input to your ROUND function and that formula results in division by zero, you’ll see this error.
Solution: Review your formula to ensure it doesn’t result in division by zero. You may need to update your formula or the values in the cells it’s referencing.
#N/A Error
Cause: The #N/A error appears when a formula can’t find a referenced value. If you’re using a lookup function as an input to your ROUND function and that lookup function can’t find the value it’s searching for, you’ll see this error.
Solution: Double-check your lookup function to ensure it’s searching for a value that exists. If the value doesn’t exist, you may need to update the value you’re searching for or the range you’re searching in.
Using ROUND With Other Google Sheets Functions
Combining the ROUND function with other Google Sheets functions can enable you to perform more complex calculations and analyses. Below, we’ll explore how to use ROUND with functions such as SUM, AVERAGE, and IF.
With SUM
Usage: If you need to sum a series of numbers and round the result to a specific number of decimal places, you can use the SUM function inside the ROUND function.
Example: Suppose you have numbers in cells A1, A2, and A3 and you want to sum these numbers and round the result to two decimal places. The formula would be =ROUND(SUM(A1:A3),2).
With AVERAGE
Usage: If you want to find the average of a group of numbers and round off the result, you can use the AVERAGE function inside the ROUND function.
Example: Suppose you have numbers in cells B1 to B5 and you want to find their average and round the result to one decimal place. The formula would be =ROUND(AVERAGE(B1:B5),1).
With IF
Usage: The IF function can be used with the ROUND function to apply conditional rounding.
Example: Suppose you have a number in cell C1 and want to round it to zero decimal places if it’s greater than 100, otherwise round it to two decimal places. The formula would be =IF(C1>100,ROUND(C1,0),ROUND(C1,2)).
With PRODUCT
Usage: If you want to multiply a series of numbers and round the result, you can use the PRODUCT function inside the ROUND function.
Example: Suppose you have numbers in cells D1, D2, and D3 and you want to multiply these numbers and round the result to three decimal places. The formula would be =ROUND(PRODUCT(D1:D3),3).
For more details on the ROUND function, check out the official documentation at the Google Docs Editors Help Center.
