using ceiling function within Google Sheets QUERY-CodePudding

I'm trying to use the QUERY function in Google Sheets to summarize the data from another sheet.

I have a sheet named Expenses in which I list expenses by description, amount (column B) and the month in which the expense is to be paid (as a month name e.g. "January")(column C).

This formula works:

=query(Expenses!B1:C, "select C, sum(B) group by C order by C")

but I would like to

order by month chronologically,
add a column that rounds the amount up to the next $100.

Apparently the regular Sheets functions are not available within QUERY since it uses the "Google Visualization API" instead.

I've tried searching that API documentation for a "ceiling" function with no success. I've also not found a way to go from month name to month ordinal.

Suggestions?

Thanks.

CodePudding user response：

try:

=ARRAYFORMULA(SPLIT(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE({
 QUERY({Expenses!B1:B, TEXT(MONTH(Expenses!C1:C&1), "00×")&Expenses!C1:C}, 
 "select Col2,sum(Col1) where Col1 is not null group by Col2 label sum(Col1)''", ), 
 ROUNDUP(QUERY({Expenses!B1:B, TEXT(MONTH(Expenses!C1:C&1), "00×")&Expenses!C1:C}, 
 "select sum(Col1) where Col1 is not null group by Col2 label sum(Col1)''", ), -2)}),,9^9)), 
 "(^\d ×)", ), " "))

CodePudding user response：

It is in fact possible to do rounding within a QUERY, but it needs to be done indirectly with a bit of maths. To do so, you have to exploit the (undocumented, I think) modulo operator (%). Replicating the answer given by player0:

=ArrayFormula(query(query(query({B:C,month(C:C&1)}, "select Col2, sum(Col1), Col3 where Col2 is not null group by Col2,Col3 order by Col3"),"select Col1,Col2,(Col2 100)-(Col2 100)0"),"offset 1",0))

What we are doing here within the query is adding 100 to each amount, then from this subtracting (amount 100) modulo 100, which rounds the answer back down to the nearest hundred (achieving the same result as you requested). It should be possible to do account for other rounding scenarios by changing the addend and modulus accordingly.