Thread: Average of specified blank cells

Hi

I want to find the average of all cells between row A1 and beneath (both blank and non blank cells), all the way down to the last cell I'm writing a value in.

Example 1.
- Here is the last cell I've written a value in, A6. I want the formula to find the average of cell A1 to A6, included the blank cells A4 and A5, but not A7 and A8.

A1 = 5
A2 = 3
A3 = 6
A4 = Blank celle
A5 = Blank celle
A6 = 2
A7 = Blank celle
A8 = Blank celle

Example 2.
- Here is the last cell I've written a value in, A7. I want to use the same formula to find the average of A1 to A7. I don't want the formula to include A8, because I haven't written any value after A7.

A1 = 5
A2 = 3
A3 = 6
A4 = Blank celle
A5 = Blank celle
A6 = 2
A7 = 5
A8 = Blank celle


I can solve the problem by writing value 0 in the blank cells in between, but I would love to have a formula where writing 0 is not needed.

(PS: I have the formula for including blank cells as 0.)
=SUMIF(A1:A8; "<>0")/COUNTIF(A1:A8; "<>0")

Not clear - if blanks are to be included in the average (ie treated as 0) then:

=SUM(A:A)/MATCH(9.99E+307;A:A)

If on the other hand you're saying blanks are to be excluded from the average then:

=AVERAGE(A1:INDEX(A:A;MATCH(9.99E+307;A:A)))

Hmm.. I get a error message on the lookup_value 9.99E. It says that the formula contains an error.

Sorry - given your locale I suspect you should be using 9,99E+307 rather than 9.99E+307

=SUM(A:A)/MATCH(9,99E+307;A:A)

Where you adding the formula ?

I had presumed you were adding this somewhere other than Column A, seemingly not.

If you're adding the formula into Column A somewhere below the data then assuming at a later point you may choose to physically insert new rows try using the below:

=SUM(A1:INDEX(A:A;ROW()-1))/MATCH(9,99E+307;A1:INDEX(A:A;ROW()-1))