Average Row Formula

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Is there a way to calculate the average of a row if some cells are
empty but when calculating the average they should have the value of
the cell to the left?

If the following data is enterered

A B C D E F G

1 3 5 6

For the calculation the cells should use the data

A B C D E F G

1 3 3 3 5 5 6

Also what would the average formula be if you wanted to skip one of the
cells from the average calculation?

Thanks

Tom
 
First you would need to fill in the blanks and then average. You can use an
average(if array formula to skip a cell(s).
 
=AVERAGE(IF(ISNUMBER(A1:G1),A1:G1,N(OFFSET(A1,0,MATCH(COLUMN(A1:G1),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1))))

array entered (Ctrl-Shift-Enter).

N(OFFSET()) is needed because OFFSET by itself sometimes misbehaves in
an array formula.

Jerry
 
Thanks Jerry the formula works great. One other question is how can
the formula be modified to eliminate one cell in calculating the
average for the row? Foe example eliminating cell B in my second
example above so the average would be calculated on the total of 6
cells not 7.

Thanks
 
=AVERAGE(IF(COLUMN(A1:G1)<>2,IF(ISNUMBER(A1:G1),A1:G1,N(OFFSET(A1,0,MATCH(COLUMN(A1:G1),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))))

also array entered. Alternately, you can remove a number from an average by

ave_without = ave - (obs-ave)/(n-1)

Jerry
 
Back
Top