Average Row Formula

[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

 

[Don Guillett]

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).

 

[Tom]

I do not want to fill in the blanks but still calculate the formula.

 

[Jerry W. Lewis]

=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

 

[Tom]

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

 

[Guest]

=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