Solution 1
Brute force:
The average that you want can be computed by:
=IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1),
IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1),
IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1),
IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1),
IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1), "oops")))))
(Single-line version:)
=IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1), IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1), IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1),"oops")))))
which looks for the shortest initial subset of the row
that contains three numbers, and averages that.
This becomes not only awkward for large numbers of columns,
but may actually fail fairly quickly,
as (I believe) IF()
has a fairly low nesting limit.
Solution 1.9
Finesse.
I call this “1.9” because it’s not done yet.
The expression
SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})
(used in an array formula)
finds the column numbers of the first three numbers on row 1.
The
IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")
part evaluates to
IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )
which reduces to
{ 1, "", 3, "", 5, 6, 7 }
and SMALL(…, {1,2,3})
of the above produces { 1, 3, 5 }
.
It should be a simple matter of saying
=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))
or
=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))
but they return either the value of A1
(12) or an error
(#DIV/0!
or #REF!
). I can’t figure out how to finish this.