Hi All

I have a sheet with production numbers for work
there are 3 columns of 35 rows.
the fields are B3 - B37 C3-C37 D3-D37
in each of these columns the first entry may or may not be in the first row
and there may be a gap from one entry in a column to the next.

I am using this formula " =AVERAGE(IF((B2:B36<"")*(B3:B37<""),B3:B37)) "
that "Tom Ogilvy" gave to me to do the following.
Find the second non blank cell in a column. And then also if there is gap in
between one cell and the next to look at the second non blank cell after
that
and continue to do this through row #37 of each column and then find the
average of sum of those numbers.



Example

A B C
1 15 25
2 27 31 11
3 21 17
4 35
6 23 18 68
7 11 7 43
8 123
9 16 54 17
10 18 11 45

etc etc etc till row 37
in this example the formula would skip
A1 A4 and A9 and give the average of the rest which I calculate to be
27+23+11+18=79
79/4= 19.75

It Would skip
B1 B6 B9 and give the average of the rest which I calculate to be
31+21+7+11=70
70/4=17.5

It would skip
C2 C6 and give the average of the rest which I calculate to be
17+43+123+17+45=245
245/5=49

Then I want to be able to get a total average of the three columns.
So I need to be able to count all the cells that the previous formula did
not skip
which in this example would be
a total of 9 cells of data

so the total average for this example would be

79+70+245=394
394/9 cells = 43.78

I know it is confusing

I hope I was able to explain this well enough and also hope that someone can
help me with this

Thanks for all the help
Dan N
--
--
crunchnin numbers

one method would be
=sumproduct(--(b4:d37<""),--(B3:d36<""),b4:d37)/sumproduct(--(b4:d37<""),--(B3:d36<""))
"belvy123" wrote:

Hi All

I have a sheet with production numbers for work
there are 3 columns of 35 rows.
the fields are B3 - B37 C3-C37 D3-D37
in each of these columns the first entry may or may not be in the first row
and there may be a gap from one entry in a column to the next.

I am using this formula " =AVERAGE(IF((B2:B36<"")*(B3:B37<""),B3:B37)) "
that "Tom Ogilvy" gave to me to do the following.
Find the second non blank cell in a column. And then also if there is gap in
between one cell and the next to look at the second non blank cell after
that
and continue to do this through row #37 of each column and then find the
average of sum of those numbers.



Example

A B C
1 15 25
2 27 31 11
3 21 17
4 35
6 23 18 68
7 11 7 43
8 123
9 16 54 17
10 18 11 45

etc etc etc till row 37
in this example the formula would skip
A1 A4 and A9 and give the average of the rest which I calculate to be
27+23+11+18=79
79/4= 19.75

It Would skip
B1 B6 B9 and give the average of the rest which I calculate to be
31+21+7+11=70
70/4=17.5

It would skip
C2 C6 and give the average of the rest which I calculate to be
17+43+123+17+45=245
245/5=49

Then I want to be able to get a total average of the three columns.
So I need to be able to count all the cells that the previous formula did
not skip
which in this example would be
a total of 9 cells of data

so the total average for this example would be

79+70+245=394
394/9 cells = 43.78

I know it is confusing

I hope I was able to explain this well enough and also hope that someone can
help me with this

Thanks for all the help
Dan N
--
--
crunchnin numbers

Hi BJ

Thanks it works great
your a life saver
--
cruchnin numbers


"bj" wrote:

one method would be
=sumproduct(--(b4:d37<""),--(B3:d36<""),b4:d37)/sumproduct(--(b4:d37<""),--(B3:d36<""))
"belvy123" wrote:

Hi All

I have a sheet with production numbers for work
there are 3 columns of 35 rows.
the fields are B3 - B37 C3-C37 D3-D37
in each of these columns the first entry may or may not be in the first row
and there may be a gap from one entry in a column to the next.

I am using this formula " =AVERAGE(IF((B2:B36<"")*(B3:B37<""),B3:B37)) "
that "Tom Ogilvy" gave to me to do the following.
Find the second non blank cell in a column. And then also if there is gap in
between one cell and the next to look at the second non blank cell after
that
and continue to do this through row #37 of each column and then find the
average of sum of those numbers.



Example

A B C
1 15 25
2 27 31 11
3 21 17
4 35
6 23 18 68
7 11 7 43
8 123
9 16 54 17
10 18 11 45

etc etc etc till row 37
in this example the formula would skip
A1 A4 and A9 and give the average of the rest which I calculate to be
27+23+11+18=79
79/4= 19.75

It Would skip
B1 B6 B9 and give the average of the rest which I calculate to be
31+21+7+11=70
70/4=17.5

It would skip
C2 C6 and give the average of the rest which I calculate to be
17+43+123+17+45=245
245/5=49

Then I want to be able to get a total average of the three columns.
So I need to be able to count all the cells that the previous formula did
not skip
which in this example would be
a total of 9 cells of data

so the total average for this example would be

79+70+245=394
394/9 cells = 43.78

I know it is confusing

I hope I was able to explain this well enough and also hope that someone can
help me with this

Thanks for all the help
Dan N
--
--
crunchnin numbers