How to Round Numbers in Google Sheets

On this put up, we are going to present you how to spherical numbers in Google Sheets. Spreadsheet information usually consists of decimal numbers that lie between complete numbers. A decimal quantity consists of a decimal level (or a dot) that separates a complete quantity from its fractional half. It’s usually a good suggestion to spherical off numbers to a sure decimal place in order that it turns into simpler to work with fractional information. Rounding is used to simplify the numbers by shortening the variety of digits on the best of the decimal level. It additionally makes the info look extra uniform or symmetric. On this put up, we are going to present you ways to spherical numbers in Google Sheets utilizing seven completely different strategies.

How to Round Numbers in Google Sheets

You’ll be able to spherical numbers in Google Sheets utilizing the next strategies:

1. Round numbers utilizing the ROUND operate.
2. Round numbers utilizing the ROUNDUP operate.
3. Round numbers utilizing the ROUNDDOWN operate.
4. Round numbers utilizing the MROUND operate.
5. Round numbers utilizing the INT operate.
6. Round numbers utilizing the FLOOR operate.
7. Round numbers utilizing the CEILING operate.

Allow us to have an in depth take a look at every of those strategies.

1] Round numbers utilizing the ROUND operate

The ROUND operate rounds a quantity to a particular variety of decimal locations as per the usual guidelines, that are as follows:

1. If the digit to the best of the rounding digit is lower than 5, the rounding digit is left unchanged (rounded down).
2. If the digit to the best of the rounding digit is bigger than or equal to 5, the digit is elevated by 1 (rounded up).

The syntax of the Round operate is:

ROUND(worth, [places])

• The place worth refers to the quantity that wants to be rounded, and
• [places] refers to the variety of decimal locations to which the quantity must be rounded. It’s an optionally available argument. If not specified by the person, it takes the worth zero (0).

Now allow us to perceive how to spherical numbers in Google Sheets utilizing the ROUND operate.

A] Round numbers to the best of the decimal level

Suppose we’ve a spreadsheet with some pattern information as proven in the above picture. The primary column lists some fractional figures that want to be rounded off to the variety of locations specified in the second column. To spherical these figures, we might use the ROUND operate as follows:

Place the cursor in cell C3 and kind the next operate:

=ROUND(A3)

Because the variety of decimal locations to which the quantity wants to be rounded isn’t specified for cell A3, it is going to take the default worth (0). This implies there isn’t a rounding digit, or in different phrases, the quantity wants to be rounded off to the closest integer. Now for the reason that digit to the best of the decimal level is 0 which is lower than 5, the quantity to the left of the decimal level is left unchanged. So the resultant worth will likely be 0, as displayed in cell C3.

For the subsequent worth (cell A4), the rounding place is 2. So the quantity wants to be rounded off to 2 decimal locations. So the rounding digit is 2. The digit to the best of the rounding digit is 3, which is lower than 5. So the rounding digit will likely be left unchanged. Due to this fact, the resultant rounded worth will likely be 1.62, as proven in cell C4.

For the subsequent worth (cell A5), the rounding place is 0. Once more, the quantity will likely be rounded off to the closest integer which is 11, as displayed in cell C5. Right here, for the reason that digit to the best of the decimal level is equal to 5, the digit to the left is raised by 1.

Now for the subsequent 2 values (in cells A6 and A7) you may simply decide how the ROUND operate is rounding the values.

B] Round numbers to the left of the decimal level

Suppose you want to around the quantity to the left of the decimal level as an alternative of the best. For that, you want to go a destructive worth in the locations argument.

A destructive worth in the locations argument will take away all of the digits to the best of the decimal level and around the quantity to the left of the decimal level to the closest tens, a whole lot, hundreds, and so forth.

For instance, take a look at the above picture. We have now handed destructive values as locations in the ROUND operate. Right here,

• -1 will around the quantity to the left of the decimal level to the closest tens.
• -2 will around the quantity to the left of the decimal level to the closest a whole lot.
• -3 will around the quantity to the left of the decimal level to the closest hundreds, and so forth.

Following this, the quantity in cell D3 (421.352) turns into 420 when rounded to the closest tens, turns into 400 when rounded to the closest a whole lot, and turns into 0 when rounded to the closest hundreds.

Equally, the quantity in cell D6 (669.005) turns into 1000 when rounded to the closest hundreds and turns into 700 when rounded to the closest a whole lot.

2] Round numbers utilizing the ROUNDUP operate

The ROUNDUP operate works identical to the ROUND operate besides that it at all times rounds the quantity upwards. The syntax of the ROUNDUP operate is:

ROUNDUP(worth, [places])

• The place worth is the quantity that wants to be rounded upward, and
• [places] refers to the variety of decimal locations to which the quantity must be rounded. It’s an optionally available argument and its default worth is zero (0). If a destructive worth is handed in the locations argument, the quantity to the left of the decimal level is rounded upward.

Now take a look on the above picture. As you may even see, all of the numbers have been rounded upward to a sure variety of decimal locations both to the best of the decimal level or to the left of the decimal level, relying on whether or not the worth of the locations argument is optimistic or destructive.

For instance, the worth in cell G4 (1.623) has been rounded up to 2 decimal locations. Right here, the rounding place is 2, which is digit 2, and the digit subsequent to 2 is 3, which is lower than 5. Nonetheless, since this can be a ROUNDUP operate, the resultant worth will likely be 1.63 and never 1.62.

Equally, the worth in cell G8 (426.352) turns into 430 (not 420) when rounded up to the closest tens.

3] Round numbers utilizing the ROUNDDOWN operate

The ROUNDDOWN operate additionally works just like the ROUND operate besides that it at all times rounds the quantity downwards.

The syntax of the ROUNDDOWN operate is:

ROUNDDOWN (worth, [places])

• The place worth is the quantity that wants to be rounded downwards, and
• [places] refers to the variety of decimal locations to which the quantity must be rounded. It’s an optionally available argument and takes the worth zero (0) if not specified by the person. The quantity to the left of the decimal level is rounded downward if a destructive worth is handed in the locations argument.

Now take a look on the above picture. Trying on the figures, you might simply perceive how the ROUNDDOWN operate is rounding the numbers down to a sure variety of decimal locations. The numbers are rounded both to the best of the decimal level or to the left of the decimal level primarily based on the worth of the locations argument (optimistic or destructive).

For instance, the worth in cell J7 (74.496) has been rounded down to 1 decimal place. Right here the rounding place is 1, which is digit 4. The digit to the best of 4 is 9, which is bigger than 5. Nonetheless, the rounded worth will likely be 74.4 and never 74.5, for the reason that ROUNDDOWN operate has been utilized to the cell worth.

4] Round numbers utilizing the MROUND operate

The MROUND operate rounds a quantity to the closest a number of of one other quantity, comparable to 2, 3, 5, and so forth.

The syntax of the MROUND operate is:

MROUND(worth,issue)

• The place worth is the quantity that wants to be rounded, and
• issue is the quantity whose a number of turns into the closest quantity to which the given quantity must be rounded.

Notes:

1. Whereas utilizing the MROUND operate, you may go a destructive worth in the issue argument provided that the worth argument can be destructive.
2. Each worth and issue arguments could be non-integral.
3. If 0 is handed in the issue argument, MROUND operate will return 0.
4. If 2 multiples of issue are equally nearest to the worth, a number of with the upper absolute worth will likely be returned.

To know this, take a look on the above picture. The worth in cell M7 (3.28) has been rounded to 3.3. Right here, the issue worth is 0.05. If we carry on multiplying the issue with 1, 2, 3 and so forth, we are going to discover the next numbers nearest to 3.28:

0.05 x 64 = 3.2

0.05 x 65 = 3.25

0.05 x 66 = 3.3

0.05x 67 = 3.35

Out of all these, the closest is 3.3. So the MROUND operate has returned 3.3 consequently.

5] Round numbers utilizing the INT operate

The INT operate is used to spherical a decimal quantity downwards. It at all times rounds the quantity down to the nearest integer which is lower than or equal to it.

The syntax of the INT operate is:

INT(worth)

• The place worth is the quantity that wants to be rounded off.

To know this, take a look on the above picture. The worth in cell P6 (24.8) has been rounded to 24, which is the closest integer lesser than 24.8. Equally, the worth in cell P7 (-16.1) has been rounded to -17, which is the closest integer lesser than -16.1.

The principle distinction between the INT operate and the ROUNDDOWN operate is that the INT operate rounds the worth of a given quantity downwards, whereas the ROUNDDOWN operate rounds the ‘absolute’ worth of a given quantity downwards. So if we apply the ROUNDDOWN operate to cell P7, the end result will likely be -16, not -17.

6] Round numbers utilizing the FLOOR operate

The FLOOR operate rounds a given quantity down to the closest a number of of one other quantity.

The syntax of the FLOOR operate is:

FLOOR(worth, [factor])

• The place worth is the quantity that wants to be rounded, and
• issue is the quantity (optimistic solely) whose a number of is the closest quantity to which the worth must be rounded. It’s an optionally available argument and its default worth is 1.

To know the FLOOR operate, take a look on the above picture. The worth in cell R5 (-17) has been rounded down to -20, which is a  a number of of 4, closest to -17. Equally, the worth in cell R3 (19) has been rounded down to 18, which is a a number of of three closest to 19.

Additionally Learn: How to Spotlight Duplicates in Google Sheets.

7] Round numbers utilizing the CEILING operate

The CEILING operate rounds a given quantity upward to the closest a number of of one other quantity.

The syntax of the CEILING operate is:

CEILING(worth, [factor])

• The place worth is the quantity that wants to be rounded, and
• issue is the quantity (optimistic or destructive) whose a number of is the closest quantity to which the worth must be rounded. It’s an optionally available argument that takes the worth 1 if not specified by the person.

If the worth is optimistic, the issue should even be optimistic. But when the worth is destructive, the issue might both be optimistic or destructive to decide in which path the values must be rounded.

For instance, take a look at the above picture. The worth in cell U5 (-17) has been rounded upward to -16, which is a a number of of 4 closest to -17. Equally, the worth in cell U3 (19) has been rounded upward to 21, which is a a number of of three closest to 19.

So this winds up how you might spherical numbers in Google Sheets. Hope you discover this handy.

Learn Subsequent: How to cease Excel from rounding numbers.