What Is ROUNDDOWN Function In Google Sheets?
The ROUNDDOWN in Google Sheets rounds down the numbers in a dataset to the nearest hundreds place, tens place, to the left of the decimal point, or to the required number of decimal places, to the right of the decimal point.
The Google Sheets ROUNDDOWN Function has other two functions available in the group, which are, the ROUND and ROUNDUP functions.
For example, consider the below table with the mathematical pi value. Now, let us round down the numbers using the ROUNDDOWN function to different place values.
Select cell C2, enter the formula =ROUNDDOWN(A2,B2), press “Enter” and drag the formula from cell C2 to C4 using the fill handle, to get the following results.
We get the output shown above, i.e., the numbers are rounded according to their respective places.
Key Takeaways
- The ROUNDDOWN in Google Sheets rounds the selected numeric values according to the number of digits allotted in the argument and it always returns numeric values for valid data selection.
- Since the function, rounds down the given real number to the specified number of digits, towards zero, it helps increase the data accuracy of the datasets and makes calculations clutter free.
- While each argument value can be a single number or an expression returning a number, we can supply the arguments to the function as array ranges. However, in such a case, we will execute the formula using the shortcut keys “Ctrl + Shift + Enter” or use the function along with the ARRAYFORMULA function, that is available only in Google Sheets and not in MS Excel.
- In the ROUNDDOWN formula’s 2nd argument, positive digits (1, 2, and 3) rounds the decimals in that location, to the right of the decimal points, negative digits (like -1, -2, and -3) round the decimals according to the multiples of 10, 100, and 1000, in the left of the decimal and ‘0’ rounds the number based on the nearest number.
Syntax
The syntax of the ROUNDDOWN formula in Google Sheets is:
The arguments of the ROUNDDOWN formula in Google Sheets are:
- value: It denotes the numerical value that we want to round down. It is a mandatory argument.
- [places]: It mentions the location or the place value at which the number is rounded. It is an optional argument.
The following table shows the digits and the reference with which the values are rounded.
| Digits | Result |
| 0 | ROUNDDOWN to the nearest number. |
| 1 | ROUNDDOWN the 1st decimal number to the right. |
| 2 | ROUNDDOWN 2 decimal numbers to the right. |
| 3 | ROUNDDOWN 3 decimal numbers to the right. |
| -1 | ROUNDDOWN to the nearest 10. |
| -2 | ROUNDDOWN to the nearest 100. |
| -3 | ROUNDDOWN to the nearest 1000. |
How To Use ROUNDDOWN Function In Google Sheets?
We can use the ROUNDDOWN Google Sheets Function in 2 ways, namely,
- Access from the toolbar.
- Enter in the worksheet manually.
Method #1 – Access From the Toolbar →
Step 1: Choose an empty cell for output → select the “Insert” tab → click the “Function” option right arrow → click the “Math” option right arrow → select the “ROUNDDOWN” function, as shown below.
The ROUNDDOWN formula appears as shown below.
Step 2: Select the argument values as cell references or cell rangeand press “Enter”.
Method #2 – Enter in the Worksheet Manually.
Step 1: Select an empty cell to display the output.
Step 2: Type =ROUNDDOWN( in the chosen cell. [Alternatively, enter =R or =ROUND and click the ROUNDDOWN function from the suggestions Google Sheets lists.]
Step 3: Enter the cell values or references as the argument values.
Step 4: Close the brackets and press Enter to obtain the results in the target cells.
Examples
We will consider some ROUNDDOWN in Google Sheets examples and wound the values to 2 decimal points, nearest integer, negative values, and remove time from date.
Example #1 – Round Down to Two Decimal Places.
The dataset given below consists of a few grocery items and their costs. Let us use the ROUNDDOWN formula on the price of the items to round down to two decimal places.
The steps to round down to two decimal places are,
Step 1: Select cell C2 and enter the formula =ROUNDDOWN(B2,2), as shown below.
Step 2: Press “Enter”. Google Sheets will give the autofill option, as shown below. We can either choose the option or use the fill handle method.
Step 3: Drag the formula from cell C2 to C9 using the fill handle, to get the following results.
Example #2 – Round Down to Nearest Integer.
The dataset consists of some random numeric values. We will select the values and round down to nearest integer.
The steps to round down to two decimal places are,
Step 1: Select cell B2 and enter the formula =ROUNDDOWN(A2) and press “Enter”, as shown below.
[Note: To round down a value to the nearest integer, we can either enter 0 for the optional argument, or not provide any value, as the function considers it as 0, by default.]
Step 2: Drag the formula from cell B2 to B5 using the fill handle, to get the following results.
Example #3 – Round a Negative Number.
Consider the dataset given below and we will round a negative number. Let us understand the dataset. We have entered the same number where we will round down the positive sign number to places ranging from -2 to +2, and then the negative sign number to places ranging from -2 to +2. Working on scenarios as such, helps us comprehend the formula better.
The steps to round down value to set places are,
Step 1: Select cell C2, enter the formula =ROUNDDOWN(A2,B2) and press “Enter” as shown below.
Step 2: Drag the formula from cell C2 to C11 using the fill handle, to get the following results.
Example #4 – Remove a Time From a Date.
The dataset consists of some important dates with the allotted time in a corporate office. Let us remove a time from a date using the ROUNDDOWN().
The steps to remove time from date value are,
Step 1: Select cell C2, enter the formula =ROUNDDOWN(B2,0) and press “Enter”, as shown below.
Step 2: Drag the formula from cell C2 to C6 using the fill handle, to get the following results.
The output is shown in column C, the time is removed but the format is seen in the result.
To change the format, select column C à select the “Format” tab à click the “Number” option right-arrow à “Date”, as shown below.
The final dataset value, is shown below.
Important Things To Note
- The ROUNDDOWN in Google Sheets rounds the values down to a specified digit.
- If the optional argument is not provided, the function considers it as 0, by default, and rounds it down to the nearest integer.
- If the [places] optional second argument is –
- Higher than ‘0’, the function rounds the values to the right.
- Equal to ‘0’, it rounds to the nearest number.
- Lesser than ‘0’, the function rounds the values to the left. -1, -2 and -3 rounds the number based on the multiples of 10, 100, and 1000, respectively.
Frequently Asked Questions (FAQs)
We often forget in which category a function falls, here, the “ROUNDDOWN” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “ROUNDDOWN” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion. Also, we can use the same path to insert the ROUND() and the ROUNDUP() functions, as they are found just above and below the ROUNDDOWN function, as seen in the above image.
A few reasons the Google Sheets ROUNDDOWN function may not work are:
The cell references selected is a text value, alpha-numeric, empty or a fraction.
We have selected the place value more than the value in the dataset.
Incorrect format of the result cells, will result in incorrect results. Therefore, check the formatting of the output cells.
Alternatively, we can find the function icon to insert the ROUNDDOWN function by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Function” option → click the “All” option right arrow → select the “ROUNDDOWN” function, as shown below.
Download Template
This article must help understand ROUNDDOWN in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is ROUNDDOWN In Google Sheets. Here we explain how to use it to rounddown the numbers in a dataset with step-wise examples. You can learn more from the following articles –
Leave a Reply