Excel average with missing values

Question

I have data like the following in cells A1:F4.

Quarter   | FY15Q4 |    FY16Q1 |    FY16Q2 |    FY16Q3 |    FY16Q4

Company A | 0.34%  |    0.48%  |    0.55%  |    0.68%  | ------

Company B   0.32%       0.36%       0.34%       0.35%     0.35%

Company C | 1.18%   |------     |-----      |-----     |

I'm trying to find the average of the most recent non-missing value from last 4 columns in each row. So for:

FY15Q4, I want the average of 0.34%, 0.32%, and 1.18%
FY16Q1, I want the average of 0.48%, 0.36%, and 1.18%
FY16Q2, I want the average of 0.55%, 0.34%, and 1.18%
FY16Q3, I want the average of 0.68%, 0.35%, and 1.18%
FY16Q4, I want the average of 0.68% and 0.33% (Company C has no data for the most recent 4Qs, so it is to be ignored from the calculation of the average)

The following array formula works as I want for FY16Q3...

{=AVERAGE(IF(COUNT(B2:E2)=0,"",INDIRECT(ADDRESS(ROW(B2:E2),MAX((B2:E2<>"")*COLUMN(B2:E2))))),IF(COUNT(B3:E3)=0,"",INDIRECT(ADDRESS(ROW(B3:E3),MAX((B3:E3<>"")*COLUMN(B3:E3))))),IF(COUNT(B4:E4)=0,"",INDIRECT(ADDRESS(ROW(B4:E4),MAX((B4:E4<>"")*COLUMN(B4:E4))))))}

But for FY16Q4, the same formula structure...

=AVERAGE(IF(COUNT(C2:F2)=0,"",INDIRECT(ADDRESS(ROW(C2:F2),MAX((C2:F2<>"")*COLUMN(C2:F2))))),IF(COUNT(C3:F3)=0,"",INDIRECT(ADDRESS(ROW(C3:F3),MAX((C3:F3<>"")*COLUMN(C3:F3))))),IF(COUNT(C4:F4)=0,"",INDIRECT(ADDRESS(ROW(C4:F4),MAX((C4:F4<>"")*COLUMN(C4:F4))))))

returns the #VALUE error value.

It seems that the AVERAGE function, which usually deals well with blank cell ("") values, is struggling because of the added complexity of the array formula.

Any suggestions on how I can make this work without either (i) using find-and-replace to replace "" with a truly blank cell; or (ii) using VBA?
Surely there must be a way to make this work using only formulas...

Could you correct the sample data for FY16Q4? From your description, it seems it should be 0.33%, not 0.35%. edit Also, are those ------- cells truly blank or do they actually have ----- in them? — user4039065
My question would be as to whether the number of companies is, in reality, only 3 (as in your example) or whether any solution needs to be extendable to work for any number of compaines. — XOR LX
@Jeeped - those are truly blank; yes, you are correct - my typo error. — Jesse
@XOR LX - you are correct, I have ~15-20 companies that we are tracking, not just 3. This was just a simplified illustration of the problem. — Jesse

Scott Craner Scott Craner · Accepted Answer · 2016-08-26T17:50:46

One method without array formulas is to use helper cells. I set up another table with the same column/row headers.

Then in the first cell I put:

=IFERROR(INDEX($A:$F,MATCH($A12,$A:$A,0),IF(MATCH(1E+99,INDEX($A:$F,MATCH($A12,$A:$A,0),1):INDEX($A:$F,MATCH($A12,$A:$A,0),MATCH(B$11,$1:$1,0)))<=MATCH(B$11,$1:$1,0)-4,NA(),MIN(MATCH(1E+99,INDEX($A:$F,MATCH($A12,$A:$A,0),1):INDEX($A:$F,MATCH($A12,$A:$A,0),MATCH(B$11,$1:$1,0))),MATCH(B$11,$1:$1,0)))),"")

Then drag it across and down to fill in the table with the correct number:

Then it is just a simple AVERAGE() formula:

=AVERAGE(B12:B14)

Which when dragged over will ignore the blank cells.

A few caveats put out by @Jeeped and @XOR LX.

This only works if the "Blank" are NOT 0 formatted as something else.
As companies and quarters are added the reference table will need the same columns and rows added.

Excel average with missing values

1 Answers