The ROUND() function is not part of Google Sheets Query. Then how do we round numbers in Google Sheets Query? Is this possible?

As far as I know, there is no Query clause that we can use to round a number. But there are two methods.

The Query FORMAT clause lets us to visually round numbers. I mean to format the numbers as rounded. If you really want to round (not format) numbers in Google Sheets Query, then I have a workaround.

In this post, I have included the FORMAT clause as well as the said workaround to round numbers in Google Sheets Query.

I have already explained how to format numbers in Query. If you haven’t read that yet, here is the link – How to Format Date, Time, and Number in Google Sheets Query.

Rounding in Query Using the Format Clause

I have two examples for rounding in Query in Google Sheets. In the first example, I will simply round a column in data. In the second example, I will round an output column of an arithmetic operation.

QUERY(data, query, [headers])

Example # 1 – Just Visually Rounding (Formatting) Numbers and Percentages

The following table (please jump to the first image below) contains the gold price performance in Europe during the last one year in specific durations (time frame) as on 25/07/2020.

The data is not important to us or may not be accurate. I’ve used it only for example purpose. We are just rounding the second and third columns in this data using Query.

I have selected this data (A1:C) because we can apply the number as well as the percentage formatting at a time. As you can see the column B contains amount (numbers) and the column C contains percentage changes.

=query(A1:C,"Select * format B '#',C '#%'")

See how I have used the format clause for rounding in Query function in Google Sheets.

Actually the number is not rounded. It’s just visually rounded. Click cell F2 or G2 to see the underlying value on the formula bar. In F2 it will be 361.67, not 362 and in G2 it will be 28.47%, not 28%.

Example # 2 – Visually Rounding (Formatting) Numbers in a Calculation Column

Here I am going to apply rounding to B*C. I mean, I am going to use the asterisk arithmetic operator in my Query formula and round that output.

=query(A1:C,"Select A, B, C, B*C label B*C'Amt' format B*C '0'",1)

In the Select clause, I have selected columns A, B, C, and B*C. The fourth column formatted using the Format clause to visually rounding numbers in Query.

Since the fourth column is an output column I have used the Label clause to label/name it.

Must Read: What is the Correct Clause Order in Google Sheets Query?

Rounding in Query Using the Round Function

As I’ve already said, in the above two examples, we were formatting numbers/percentages to make it appear as rounded. If you really want to do rounding in Query, you may please follow the below workaround.

Formula as per Example # 1

If you follow the example # 1 above, the Query formula to round numbers in cell E1 would be as follows.

=ArrayFormula(query({A1:A,round(B1:B),round(C1:C,2)},"Select * where Col1 is not null label Col2'Amount', Col3'%'"))

The Round function has been used in the Query Data.

There are three columns – Change, Amount, and %. We want to keep the first column as it is as it’s a text column. The second column contains numbers and the third column contains percentage values.

To round the number column we can use any of the ROUND, ROUNDUP, or ROUNDDOWN functions. Regarding the percentage column, even though we can use the above three functions, there is a slight difference in usage.

For example, if you want to round a value to 0 decimal places, to round a number column you can use round(B1:B) whereas to round a percentage column use round(C1:C,2). The same has been explained here in detail – How to Round Percentage Values in Google Sheets.

Formula as per Example # 2

Here you need to use multiple Query formulas as combined. It’s simple to understand though. Let’s start with this Query in cell E1.

=QUERY(A1:C,"Select A, B, C, B*C",1)

Here we want to round the product column H (the 4th column) in Query, right?

Steps to Round Product or Any Formula Output Column in Query

Step 1: Use another Query (wrap the existing one) to extract the first three columns (formula to be inserted in cell E1).

E1 Formula

=QUERY(QUERY(A1:C,"Select A, B, C, B*C",1),"Select Col1, Col2, Col3",1)

Step 2: Extract the fourth column as above and round it (formula to be inserted in cell H1).

H1 Formula

=ArrayFormula(round(QUERY(QUERY(A1:C,"Select A, B, C, B*C",1),"Select Col4",1),0))

Step 3: Combine Step 1 and 2 formulas as per the syntax {step 1, step 2}, which involves the Curly Braces.

For that, cut the formula in cell H1 and combine it with the cell E1 formula as per the syntax above.

E1 Formula

={QUERY(QUERY(A1:C,"Select A, B, C, B*C",1),"Select Col1, Col2, Col3",1),ArrayFormula(round(QUERY(QUERY(A1:C,"Select A, B, C, B*C",1),"Select Col4",1),0))}

Step 4: Use the above as Query Data and label the fourth column.

=query({QUERY(QUERY(A1:C,"Select A, B, C, B*C",1),"Select Col1, Col2, Col3",1),ArrayFormula(round(QUERY(QUERY(A1:C,"Select A, B, C, B*C",1),"Select Col4",1),0))},"Select * where Col1 is not null label Col4'%'",1)

The above formula in cell E1 rounds numbers in Google Sheets Query.

That’s all. Enjoy!