Averaging non blanlk cells???

[Guest]

Hi All

I have a sheet that I want to use for production numbers at work ,and I need
to be able to average them.
The sheet has 3 columns of data for 3 different presses production.
The cell range is B1 and B2 are a heading B3 is Blank. The data will be
entered in cell.
B4 to B38. this is also the same for column C and column D
The first entry in each column will not be used to figure average production
and the first row in each column may or may not contain an entry. Also there
may be an empty cell from one cell to the next as a press may skip a shift of
production on a certain job and then continue it the next shift
SO. What I need is a formula that will find the 2nd non blaqnk cell in a
column as well as find the 2nd non blank cell after there is a gap between
cells.
Then I need this formula to find the average of all the sum of all the 2nd
non blank cells in that column ( see example below)

Example

B C
1 Pressline Pressline
2 job name jobname
3
4 11
5 35
6 23 24
7 11 12
8
9 16 45
10 18
11 32
12 10

etc etc etc till row 38
in this example the formula would skip
A4 and A9 and give the average of the rest which I calculate to be
35+23+11+18=87
87/4= 21.75

Also in column C it would skip
A6 A9 and A11 and give the average for the rest which I calculate to be
12+10=22
22/2=11
I was given a formula that does this it is
=AVERAGE(IF((B3:B37<>"")*(B4:B38<>""),B4:B38))
entered as an array
it works great as long as there is data in B4 or if there are 2 cells
(example) B6- B7 with data. If this is not the case it returns a #div/0!
error if you have say just one cell with data in, say cell B8 and then it is
blank to say cell B11
I hope I have explained this well enough

Thanks
Dan N

 

[Guest]

try
=if(iserror(AVERAGE(IF((B3:B37<>"")*(B4:B38<>""),B4:B38))),"",AVERAGE(IF((B3:B37<>"")*(B4:B38<>""),B4:B38)))
this will give a blank cell if there is no avaerage available.

 

[Guest]

Hello BJ

If you were here I would kiss you!!!!
LOL

Thanks soooo very much. I was thinkin It was a lost cause