0

I have a row of values and want to find the average of the first 3, not counting blank cells. Example:

  A      B  C      D  E  F  G
 12  blank  8  blank  7  9  4

The average of the first 3 non-blank cells should be 9, but I can't find a formula that will calculate this for me. Can anyone help ?

0

3 Answers 3

1

In A2 enter:

=IF(A1="",0,1)

and in B2 enter:

=IF(OR(B1="",SUM($A$2:A2)=3),0,1)

and copy across. Then in another cell enter:

=SUMPRODUCT((1:1)*(2:2))/3

enter image description here

0

You're looking for the AVERAGE function. According to the formula syntax and usage for Excel's AVERAGE function:

If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

The formula =AVERAGE(A1:E1) returns 9:

enter image description here

2
  • Well, if it will always be the case that A1=12, B1=blank, C1=8, D1=blank, and E1=7, then the OP doesn't need a formula; he knows that the answer is 9.  I think the point is that the OP doesn't know in advance which cells will have numbers and which will be blank.  So, if B1 has a number it (as well as A1 and C1), then the desired result is AVERAGE(A1:C1), and your answer yields an incorrect result. May 17, 2015 at 3:45
  • The OP clearly stated I can't find a formula that will calculate this for me. Like any Excel user, he needs the correct formula and proper application of the formula (e.g. what range to average) to produce his desired result. May 17, 2015 at 13:14
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .