0

I'm joining two queries together using an array: ={query(A1:B,"Select A where B is not null");query(C1:D,"Select C where D is not null")}. I have it wrapped in an iferror() that, if there is no data in the second query, just completes the first query, i.e., =iferror({query(A1:B,"Select A where B is not null");query(C1:D,"Select C where D is not null")},query(A1:B,"Select A where B is not null"))

Finally, I put the above formula into a =forecast(), where I then get an issue. =forecast(E1,iferror({query(A1:B,"Select A where B is not null");query(C1:D,"Select C where D is not null")},query(A1:B,"Select A where B is not null")),iferror({query(A1:B,"Select B where B is not null");query(C1:D,"Select D where D is not null")},query(A1:B,"Select B where B is not null")))

This is throwing a red underline at the comma before the last query, stating that iferror() only takes 2 arguments and that this is the 3rd/4th. It seems to be counting each query contained within the brackets {} as a separate argument to the function. Strangely, it doesn't actually throw an error; the function still runs and has an output.

How can I resolve this syntax issue?

1
0

The warning the formula editor shows is probably because of an issue it has with { array expressions }. You can safely ignore the warning.

You can simplify the formula like this:

=forecast( 
  E1, 
  query( 
    { A1:B; C1:D }, 
    "select Col1 where Col2 is not null", 
    0 
  ), 
  query( 
    { A1:B; C1:D }, 
    "select Col2 where Col2 is not null", 
    0 
  ) 
)

...or like this:

=forecast( 
  E1, 
  filter( 
    { A1:A; C1:C }, 
    len( { B1:B; D1:D } ) 
  ), 
  filter( 
    { B1:B; D1:D }, 
    len( { B1:B; D1:D } ) 
  ) 
)

But the simplest solution would be to create a helper table like this:

=filter( 
  { A1:B; C1:D }, 
  len( { B1:B; D1:D } ) 
)

...and then use the two columns as arguments to forecast().

2
  • Thanks for clarifying that I can ignore the warning. Is there no way to actually get rid of it? My actual implementation has a few nuances that prevent me from being able to use the simplifications above, namely that the queries are referencing different sheets, but those points are well taken.
    – Michael
    Jun 22 at 16:24
  • The formulas above will work regardless of whether the data is in the same sheet or in different sheets as long as all data is in the same spreadsheet file. The first formula will continue to work when the data is brought in with importrange() from more than one spreadsheet file as well. Jun 22 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.