How to do complex calculations inside query formulas?

2
votes

In Google sheets, I get #NA with no explanation when I try to calculate the change between two sums - that I have just calculated in that same formula.

Is that not possible using query or am I doing it wrong?

  • My formula: =query(B5:H16,"select ((sum(G)/sum(F)) - (sum(D)/sum(C))) / (sum(D)/sum(C))")

The first part is working: =query(B5:H16,"select ((sum(G)/sum(F)) - (sum(D)/sum(C)))

I'm trying to do the same calculation as the formula in A3 in the screenshot - but within the query formula

Spreadsheet link: https://docs.google.com/spreadsheets/d/1VrYO-TayV6TD-iHUDT3Axgkr3gI4s9OHvj0-yW59wRA/edit#gid=0

screenshot of spreadsheet with formulas and data

1
google-sheetsgoogle-sheets-formulagoogle-query-language
share a copy of your sheet with example of desired result – player0
Done! Good point :) – Sigurd
any reason why query is needed for this? – player0
Yes. In the real data I fetch data from many columns onto one row. So if I can handle all the calculations within just one formula per row it'll make everything easier – Sigurd

1 Answers

0
votes

Wouldn't this be equivalent to the formula you are trying to get?:

=query(B5:H16, "select (sum(G)/sum(F))/(sum(D)/sum(C)) - 1")

I hope this is of any help.