B
belvy123
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.
What I want to be able to do is have a formula that will 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 continuw to do this through row #37 of each column and then find the
average of sum of those numbers.
Example
A
1 15
2 27
3
4 35
6 23
7 11
8
9 16
10 18
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
Tom Ogilvy answered the question for me with this formula
=AVERAGE(IF((B2:B36<>"")*(B3:B37<>""),B3:B37))
Entered with Ctrl+Shift+Enter
This works very very well except in one scenario.Say for instance you have
data in the first row of the column "B" and then you have data in the second
row of column "C" and the second row of column "D".
Until I add data in another row in column "C" and "D" it give me a
#DIV/0! error at the bottom of column "C" and "D"
B C D
1 100
2 250 220
3
4
5
#DIV/0! #DIV/0!
My question is....
how do I use the formula and not have an error show when there is no data in
the first row and there is not more than one item of data in the column
Thanks for all the help it is appreciated
Dan N
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.
What I want to be able to do is have a formula that will 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 continuw to do this through row #37 of each column and then find the
average of sum of those numbers.
Example
A
1 15
2 27
3
4 35
6 23
7 11
8
9 16
10 18
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
Tom Ogilvy answered the question for me with this formula
=AVERAGE(IF((B2:B36<>"")*(B3:B37<>""),B3:B37))
Entered with Ctrl+Shift+Enter
This works very very well except in one scenario.Say for instance you have
data in the first row of the column "B" and then you have data in the second
row of column "C" and the second row of column "D".
Until I add data in another row in column "C" and "D" it give me a
#DIV/0! error at the bottom of column "C" and "D"
B C D
1 100
2 250 220
3
4
5
#DIV/0! #DIV/0!
My question is....
how do I use the formula and not have an error show when there is no data in
the first row and there is not more than one item of data in the column
Thanks for all the help it is appreciated
Dan N