Average from first non-blank cell

[Nick M]

I am trying to get a 12 cell average within a row, starting with the first
non-blank entry, working from left to right. All entries are numeric.
The following (inelegant) formula works, but is limited by the number of
conditions allowed in Excel.
=IF(H17<>"",AVERAGE(H17:S17),IF(I17<>"",AVERAGE(I17:T17),IF(J17<>"",AVERAGE(J17:U17),IF(K17<>"",AVERAGE(K17:V17),IF(L17<>"",AVERAGE(L17:W17),IF(M17<>"",AVERAGE(M17:X17),IF(N17<>"",AVERAGE(N17:Y17),AVERAGE(O17:Z17))))))))
Can anybody assist with a more elegant solution that will give the correct
average even if the last entry is more than 7 blanks away?
Thanks

 

[Chris Bode via OfficeKB.com]

try following macro code

Sub Calc_Average()
Dim row As Integer, col As Integer
row = 1
col = 1

Dim sum As Double
sum = 0

For col = 1 To 12
If Sheet1.Cells(row, col).Value <> "" Then
sum = sum + CDbl(Sheet1.Cells(row, col).Value)
End If
Next

Dim avg As Double
avg = sum / col

MsgBox "Average is = " & avg
End Sub

Chris

 

[Ron Rosenfeld]

I am trying to get a 12 cell average within a row, starting with the first
non-blank entry, working from left to right. All entries are numeric.
The following (inelegant) formula works, but is limited by the number of
conditions allowed in Excel.
=IF(H17<>"",AVERAGE(H17:S17),IF(I17<>"",AVERAGE(I17:T17),IF(J17<>"",AVERAGE(J17:U17),IF(K17<>"",AVERAGE(K17:V17),IF(L17<>"",AVERAGE(L17:W17),IF(M17<>"",AVERAGE(M17:X17),IF(N17<>"",AVERAGE(N17:Y17),AVERAGE(O17:Z17))))))))
Can anybody assist with a more elegant solution that will give the correct
average even if the last entry is more than 7 blanks away?
Thanks

This formula must be **array-entered**:

=AVERAGE(OFFSET(A1,0,MATCH(TRUE,ISNUMBER(1:1),0)-1,1,12))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If your first "non-blank" might not be in A1, in other words, if you might have
labels in Column A that are numeric, you might need to modify the range
references a bit. If those values are not numeric, then you will only need to
modify the row numbers.

--ron

 

[Nick M]

Hi Chris
Thanks for the reply
Unfortunately, I am not familliar with macros. Tried copy paste into the
macro function but got runtime error 13.
Regards

 

[Nick M]

Tried your formula but it returns incorrect averages
Possibly look at the workbook I have sent you in case I have not entered the
ranges correctly
Thanks

 

[Ron Rosenfeld]

Tried your formula but it returns incorrect averages
Possibly look at the workbook I have sent you in case I have not entered the
ranges correctly
Thanks

"Ron Rosenfeld" wrote:

My newsreader does not download attachments.

Just post in plain text:
a sample of your data with cell addresses and values
a copy/paste of the formula you are using
the result you are obtaining
the result you expect.
--ron

 

[Nick M]

Average of 12 cells, starting from first non-blank cell entry, working L to R
B = blank cell
average in column A

Col A B C D E F G H I J K L M N O P Q R S T U
5.66 = B B B B B 4 1 0 -1 5 6 15 3 9 9 9 8 1 3 4
3.25 = 2 3 6 8 3 2 0 0 0 1 6 8 9 5 6 7 4 1 9 0
2.25 = B B 6 5 4 3 3 6 B B B B B B B B B B B B
2.33 = B B B B B B B 1 5 6 -7 2 2 1 4 5 5 1 3 7

I hope this stays in a grid pattern.
Thanks for your patience

 

[Ron Rosenfeld]

Average of 12 cells, starting from first non-blank cell entry, working L to R
B = blank cell
average in column A

Col A B C D E F G H I J K L M N O P Q R S T U
5.66 = B B B B B 4 1 0 -1 5 6 15 3 9 9 9 8 1 3 4
3.25 = 2 3 6 8 3 2 0 0 0 1 6 8 9 5 6 7 4 1 9 0
2.25 = B B 6 5 4 3 3 6 B B B B B B B B B B B B
2.33 = B B B B B B B 1 5 6 -7 2 2 1 4 5 5 1 3 7

I hope this stays in a grid pattern.
Thanks for your patience

And what is your formula in Column A?
--ron

 

[Ron Rosenfeld]

Average of 12 cells, starting from first non-blank cell entry, working L to R
B = blank cell
average in column A

Col A B C D E F G H I J K L M N O P Q R S T U
5.66 = B B B B B 4 1 0 -1 5 6 15 3 9 9 9 8 1 3 4
3.25 = 2 3 6 8 3 2 0 0 0 1 6 8 9 5 6 7 4 1 9 0
2.25 = B B 6 5 4 3 3 6 B B B B B B B B B B B B
2.33 = B B B B B B B 1 5 6 -7 2 2 1 4 5 5 1 3 7

I hope this stays in a grid pattern.
Thanks for your patience

Also, with regard to your results,

Row 1: Result is 5.6666666... or arithmetically rounded would be 5.67
Row 3: The average of the numbers : 6 5 4 3 3 6 is 4.5, not 2.25
--ron

 

[Ron Rosenfeld]

Average of 12 cells, starting from first non-blank cell entry, working L to R
B = blank cell
average in column A

Col A B C D E F G H I J K L M N O P Q R S T U
5.66 = B B B B B 4 1 0 -1 5 6 15 3 9 9 9 8 1 3 4
3.25 = 2 3 6 8 3 2 0 0 0 1 6 8 9 5 6 7 4 1 9 0
2.25 = B B 6 5 4 3 3 6 B B B B B B B B B B B B
2.33 = B B B B B B B 1 5 6 -7 2 2 1 4 5 5 1 3 7

I hope this stays in a grid pattern.
Thanks for your patience

Except for the discrepancy in results, especially with regard to Row 3, I would
have modified the formulas to:

=AVERAGE(OFFSET(A1,0,MATCH(TRUE,ISNUMBER(B1:Z1),0),1,12))

again --- **array-entered** as previously described.
--ron

 

[Nick M]

Thats the question I need answered!
I calculated these manually.
Oops, you are correct, the result in the first row should be 5.666666
and the 3rd row s/be 4.5

 

[Ron Rosenfeld]

Thats the question I need answered!

When you wrote "Tried your formula but it returns incorrect averages", it would
have been helpful to know exactly what formula you tried! And what answers you
were obtaining and what answers you expected.

I calculated these manually.
Oops, you are correct, the result in the first row should be 5.666666
and the 3rd row s/be 4.5

--ron

 

[Nick M]

It Works!
Fantastic!
I have been wasting my time calculating this manually for 5 years
Thanks Ron

 

[Ron Rosenfeld]

It Works!
Fantastic!
I have been wasting my time calculating this manually for 5 years
Thanks Ron

You're welcome. Glad to help. Thanks for the feedback.
--ron