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(I1 7:T17),IF(J17<"",AVERAGE(J17:U17),IF(K17<"",AVER AGE(K17:V17),IF(L17<"",AVERAGE(L17:W17),IF(M17<" ",AVERAGE(M17:X17),IF(N17<"",AVERAGE(N17:Y17),AVE RAGE(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

try following macro code

Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200903/1

On Mon, 9 Mar 2009 01:02:01 -0700, Nick M
wrote:

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(I 17:T17),IF(J17<"",AVERAGE(J17:U17),IF(K17<"",AVE RAGE(K17:V17),IF(L17<"",AVERAGE(L17:W17),IF(M17< "",AVERAGE(M17:X17),IF(N17<"",AVERAGE(N17:Y17),AV ERAGE(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

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

"Chris Bode via OfficeKB.com" wrote:

try following macro code

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
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200903/1

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:

On Mon, 9 Mar 2009 01:02:01 -0700, Nick M
wrote:

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(I 17:T17),IF(J17<"",AVERAGE(J17:U17),IF(K17<"",AVE RAGE(K17:V17),IF(L17<"",AVERAGE(L17:W17),IF(M17< "",AVERAGE(M17:X17),IF(N17<"",AVERAGE(N17:Y17),AV ERAGE(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

On Mon, 9 Mar 2009 08:10:00 -0700, Nick M
wrote:

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

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" wrote:

On Mon, 9 Mar 2009 08:10:00 -0700, Nick M
wrote:

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

On Mon, 9 Mar 2009 11:37:01 -0700, Nick M
wrote:

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

On Mon, 9 Mar 2009 11:37:01 -0700, Nick M
wrote:

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

On Mon, 9 Mar 2009 11:37:01 -0700, Nick M
wrote:

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

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" wrote:

On Mon, 9 Mar 2009 11:37:01 -0700, Nick M
wrote:

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

On Mon, 9 Mar 2009 12:36:39 -0700, Nick M
wrote:

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

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

"Ron Rosenfeld" wrote:

On Mon, 9 Mar 2009 11:37:01 -0700, Nick M
wrote:

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

On Tue, 10 Mar 2009 00:13:01 -0700, Nick M
wrote:

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