Search code examples
google-sheetsaverage

Ignore certain values when computing AVERAGE()

I use AVERAGE() in part of a formula where I VLOOKUP the values from another sheet.

I want to cover the case where the lookup has no match. I do not want to set it to 0, I just want to ignore it in the average calculation.

I've thought about it for some days now, and can't come up with something elegant:

AVERAGE(
  VLOOKUP(TEXT(today()-2,"yyyy-MM-dd"),indirect("marketHistory"&A6&"!$A$1:$F"),6,false),
  VLOOKUP(TEXT(today()-3,"yyyy-MM-dd"),indirect("marketHistory"&A6&"!$A$1:$F"),6,false),
  VLOOKUP(TEXT(today()-4,"yyyy-MM-dd"),indirect("marketHistory"&A6&"!$A$1:$F"),6,false)
)

Edit:

The data VLOOKUP is searching:

2018-08-18  47999.96    47999.96    47999.96    9   28076
2018-08-19  47799.96    47799.96    47799.96    5   17915
2018-08-20  40002.78    40002.78    40002.78    3   853

Averaging last column, looking for 2018-08-19, 2018-08-18 and 2018-08-17. Expected: 22995,5 (average of values for 2018-08-19 and 2018-08-18).

I tried enclosing the VLOOKUPs with IFERROR like this:

=average(IFERROR(VLOOKUP(J18,I19:J20,2,false),"error"),J13,K13)

but I get the error:

Function AVERAGE parameter 1 expects number values. But 'error' is a text and cannot be coerced to a number.

If I split the whole thing up, it works.

Solution

  • Here's a demo of what you may need:

    google sheet screenshot

    • data in A:B
    • your input in D2:D — the list of values to vlookup

    The key is to get filtered list from a column with values. The filter + regex handles the task.

    The formula: =FILTER(B:B,REGEXMATCH(TO_TEXT(A:A),TEXTJOIN("|",1,D2:D))) gives a list of values matching selected dates:

    28076
17915

    Then do any operation with returned values:

    =AVERAGE(FILTER(B:B,REGEXMATCH(TO_TEXT(A:A),TEXTJOIN("|",1,D2:D))))