Trying to find the average of the first 3 values in the column.

I'm already running a query to find the average data within the last 3 days

=average(query({$A$10:$A$107,$E$10:$E$107},"Select avg(Col2) where dateDiff(now(),Col1) < 4 group by Col1"))

Column A has dates, column E has sales, values begin in row 12

but on occasion, the data is not up to date and gives an error if the last 3 numbers fall outside of the dates.

I want to add an iferror equation to get the next 3 averages.

This is also due to the spreadsheet starts at the beginning of the month at the bottom and the end of the month at the top. So there are a lot of blanks to skip.

I tried array and offset but I'm not getting the correct result.

  • 2
    Please update your question. There are no numerical examples of expected versus actual results. There is no spreadsheet example either, that would be much clearer than having to figure out the text description of your sheet.
    – user55949
    May 2 '19 at 14:34
  • share a copy of your sheet
    – user0
    May 2 '19 at 19:37

You could try filtering out the blanks in advance? I'm not sure if this is exactly what you want to achieve, but let me know.

=average(query(FILTER({$A$10:$A$107,$E$10:$E$107},$A$10:$A$107<>""),"Select avg(Col2) where dateDiff(now(),Col1) < 4 group by Col1"))

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.