CODEWITHSUNDEEP
excelexcel-formula
Andreas
Andreas

Reputation: 23958

How can I get an average formula that omits errors

I'm trying to get the average of four data points.
The problem is that one or more data points could be missing.

The average should be the average of the last four mondays or four last tuesdays etc.
Each data point is about 1000 rows apart so my idea was to "list" the dates needed and use vlookup and average.

Generic formula 

// I only add two dates, but the same formula is repeated for four dates
=AVERAGE(VLOOKUP(DATE_1;Table;25;FALSE);VLOOKUP(DATE_2;Table;25;FALSE))

The DATE_1 and DATE_2 is dynamic calculations of the previous two, lets say mondays.
This works if all dates are there, but if one monday is missing VLOOKUP returns an error and the error can't be calculated as an average.
I figured I could wrap VLOOKUP with IFERROR, but I can't get that working either 

// for simplicity I removed the average and only show one. 
IFERROR(VLOOKUP(DATE_1;Table;25;FALSE);"") // returns empty string, can't calculate

IFERROR(VLOOKUP(DATE_1;Table;25;FALSE);0) // Works, but it skews the result with a zero.

I know AVERAGE skips empty cells, but how can I "emulate" a empty cell. "" is empty string and that is not the same.
Is there formula that can handle errors and still give me the average, or a formula that returns "empty cell"?

Upvotes: 3

Views: 684

Answers (4)

Chronocidal
Chronocidal

Reputation: 7951

This is the whole point of the AGGREGATE function.

Instead of AVERAGE(SomeRange), use AGGREGATE(1, 6, SomeRange). Instead of AVERAGE(Value1, Value2), use AGGREGATE(1, 6, Value1, Value2)

The 1 tells AGGREGATE to calculate the AVERAGE, and the 6 tells it "Ignore error values". A full list of the values is at the bottom of this post

=AGGREGATE(1,6,VLOOKUP(DATE_1;Table;25;FALSE);VLOOKUP(DATE_2;Table;25;FALSE))

(As people have pointed out, this doesn't quite work properly without interim calculation cells - when you use a Formula in the function, Excel refuses to accept it in Reference Form)

Reference form: AGGREGATE(function_num, options, ref1, [ref2], …)
Array form: AGGREGATE(function_num, options, array, [k])

Function_num | Function 
      1      | AVERAGE
      2      | COUNT
      3      | COUNTA
      4      | MAX
      5      | MIN
      6      | PRODUCT
      7      | STDEV.S
      8      | STDEV.P
      9      | SUM
     10      | VAR.S
     11      | VAR.P
     12      | MEDIAN
     13      | MODE.SNGL
     14      | LARGE
     15      | SMALL
     16      | PERCENTILE.INC
     17      | QUARTILE.INC
     18      | PERCENTILE.EXC
     19      | QUARTILE.EXC

  

Option | Behaviour
   0   | Ignore nested SUBTOTAL and AGGREGATE functions
   1   | Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions 
   2   | Ignore error values, nested SUBTOTAL and AGGREGATE functions
   3   | Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
   4   | Ignore nothing
   5   | Ignore hidden rows
   6   | Ignore error values
   7   | Ignore hidden rows and error values

Upvotes: 2

Justyna MK
Justyna MK

Reputation: 3563

Here's one more solution for you to try (array formula - Ctrl+Shift+Enter):

=AVERAGE(IF(ISNUMBER(MATCH($A$1:$A$10,CHOOSE({1,2,3,4},DATE_1,DATE_2,DATE_3,DATE_4),0)),$B$1:$B$10))

Result:

enter image description here

Upvotes: 2

JLCH
JLCH

Reputation: 803

Just giving you a different approach even though it is not exactly the same way as your question is going, I just thought to share how i've solved a similar issue.

No lookup table in this one, I personnally try to avoid these in these situations, as you always have to update them given some conditions.

{=AVERAGE(IF((WEEKDAY(A1:A276,2)=1)*((L1:L276)>0)*((A1:A276)>((TODAY())-29)),L1:L276,""))}

array formula, so ctrl+shift+enter

(WEEKDAY(A1:A276,2)=1) tests if it's a monday

(L1:L276)>0) is where I have values and therefore want to ignore zeros

((A1:A276)>((TODAY())-29)) added this one for you to check if it's less than 4 weeks old

if these conditions are fulfilled the respective value in L:L is take for the average (A:A being the date in this example)

Upvotes: 2

riskypenguin
riskypenguin

Reputation: 2199

INDEX & MATCH doesn't produce the same problem, try this:

=AVERAGE(INDEX(YourColumnRange;MATCH(DATE_1;Table));INDEX(YourColumnRange;MATCH(DATE_2;Table)))

EDIT: To match your dataset you can manually calculate an average like this:

=SUM(IFERROR(INDEX(Y:Y,MATCH(Date_1,A:A,0)),0), IFERROR(INDEX(Y:Y,MATCH(Date_2,A:A,0)),0))/
COUNT(INDEX(Y:Y,MATCH(Date_1,A:A,0)), INDEX(Y:Y,MATCH(Date_2,A:A,0)))

This will allow you to skip empty rows. The formula is simplified for a generic case to make it easier to read.

Upvotes: 1

Related Questions