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
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.
The link to my example spreadsheet is https://docs.google.com/spreadsheets/d/1b3APKeWQ9UjJp4vRx_JDQWv8tN5_caYPcTIaCcGto34/edit?usp=sharing
It contains three sheets:
monthly_expenses provides a list of expenses that recur and are the same every month.
other_expenses is a list of expenses that occur a small number of times over the course of the year. The last 12 entries are transcribed from monthly_expenses.
expenses_by_month should have one line per month, in chronological order, with the total for each month. The third column should provide the amount from the previous column but rounded up to the next multiple of $100. It is generated by a query:
=query(other_expenses!B1:C,"select C, sum(B), 100*((sum(B)+99.99)%100) group by C order by C", -1)
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")
What I don't know how to do is
order the rows chronologically rather than alphabetically
compute the third column correctly. Clearly % is the wrong operation, but I've not figured out how to truncate.
but I would like to order the rows chronologically rather than alphabetically
Special recognition to The God of Biscuits for their answer.
player0's answer and comments were also very helpful.
I eventually came up with:
=ARRAYFORMULA(QUERY(QUERY(QUERY({other_expenses!B:C, TEXT(MONTH(1&other_expenses!C:C), "00")},"select sum(Col1), Col2, Col3 where Col1 IS NOT NULL GROUP BY Col2, Col3 ORDER BY Col3", ),"select Col2, Col1, Col1 + 100 - ((Col1 + 100) % 100)"),"select * LABEL Col1 'month', Col2 'total', Col3 'transfer amount'"))
Why are here three nested queries?
The innermost query adds the month number and performs the aggregation and sorting.
The query around that just adds a column for the rounded up amount. This didn't need to be a separate query but allowed me to use a column reference rather than an expression to abstract the SUM.
The outermost query just adds column labels. I couldn't figure out how to refer to the third column of the second query in a labels clause for that query.
Key observations:
The query language doesn't have a ceiling operator, but it does have remainder/mod (%), so rounding X up to the next $100 can be done by X + 100 - ((X + 100) % 100).
The query language doesn't have a way to convert a month name string like "January" to a month number like 1, but Google Sheets has a way to do it. The month number needs to be computed in Google Sheets and passed in to the query. I was unable to find good documentation for that so I got it from your suggestions plus alot of trial and error.