column averages
Posted by Brian on June 13, 2001 4:07 AM
I have a column of numbers seperated at irregular intervals by a zero. I need to get an average of the numbers between the zeros. I hope that makes sense and any help is appreciated.
Posted by ZEN on June 13, 2001 4:55 AM
do you need all the instances of the averages put into different cells?, what exactly are you trying to achieve.
zen
Posted by brian on June 13, 2001 4:59 AM
Yes, I want to generate another column containing only the averages of the numbers between the zeros
in the first column.
Posted by Aladin Akyurek on June 13, 2001 7:21 AM
until you get a better proposal.
Lets take the following sample that occupies A1:A12:
{"Nums";1;2;3;0;4;5;6;7;0;8;9}
where zeroes are literally zeroes.
In B2 enter: =CELL("address",A2)
In B3 enter: =IF(OR(ISBLANK(A3),A3=0),AVERAGE(INDIRECT(B2&":"&CELL("address",A2))),IF(ISNUMBER(B2),CELL("address",A3),B2))
Copy down the last formula as far as needed.
Aladin
==========================
