2

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
Sigurd
  • 93
  • 1
  • 5

1 Answers1

0

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.

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Thank you. It works great in the example sheet. But when I add this part (sum(K)/sum(J))/(sum(H)/sum(G))-1 in front of the where clause in a longer formula, it doesn't work anymore - but return #NA. Like this: query('sheet'!A:Z,"select sum(A),sum(B),'1',sum(B)/sum(A),'all c',F,sum(G),sum(H),sum(H)/sum(G),sum(J),sum(K),sum(K)/sum(J),(sum(J)-sum(G))/sum(G),(sum(K)-sum(H))/sum(H),(sum(K)/sum(J))/(sum(H)/sum(G))-1 where F matches '"&join("|",'sheet'!F5:F15)&"' group by F") – Sigurd Jan 27 '20 at 16:05
  • @Sigurd, you're welcome. In this case I'd suggest you to create a new question explaining what you want to accomplish, since this was not part of the original question. – Iamblichus Jan 28 '20 at 08:12