Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-queryforecast

Google sheets dynamic Forecast on Query gives #Value Error

I'm trying to use a dynamic Forecast formula on a Query in Google sheets and it gives the following # VALUE Error:

"Error Function FORECAST parameter 1 expects number values. But 'QUERY ALL ORDERS date' is a text and cannot be coerced to a number."

However, it gives a result, sees the images. I need some help to perfect the outcome without the #VALUE Error.

The Query is as follows:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/............/edit#gid=.......","query!A:B"), "SELECT Col1, SUM(Col2), COUNT(Col2) WHERE Col1 IS NOT null GROUP BY Col1 ORDER BY Col1 ASC")

The Dynamic Forecast filter is as follows:

=ArrayFormula(FORECAST(filter(A2:A,(N(B2:B)=0)*(A2:A>0)),filter(B2:B,B2:B<>""),filter(A2:A,B2:B<>"")))

Here is what it looks like:

enter image description here

Do I need to format the Query? Change the Forecast formula?

Solution

  • try:

    =ARRAYFORMULA(FORECAST(FILTER(DATEVALUE(A2:A), (N(B2:B)=0)*(A2:A>0)), 
 FILTER(B2:B, B2:B<>""), FILTER(A2:A, B2:B<>"")))

    the error means that those dates in column A are most likely formatted as Plain text instead of Date or Automatic