Identifying first non blank cell in 3 different columns
 

I have a sheet that I use for production numbers at work

There are 3 columns of 37 rows
I want to be able to find the second non blank cell in each column and then
get an average from the sum of the second cell to 37th cell in that column
and be able to do this for each column.
However I am not able to figue out how to do this as the second non blank
cell in each column may be different. ( Column one the second cell may be in
row 4 and in Column 2 the second cell may be in row 2 and in Column 3 the
secind cell may be in row 6) The reason that I want the second cell is that
the first cell of production numbers are never used to calculate average
production.
Please help
Thanks

Dan N
--
cruchnin numbers

Identifying first non blank cell in 3 different columns
 

Try this:

=AVERAGE(A37:INDEX(A1:A37,MAX(INDEX((A1:A37="")*(R OW(A1:A37)),,))+1))

Copy across

I'm assuming that the 2nd blank cell will never be the last cell in the
range!!!!!!

Biff

"belvy123" wrote in message
...
I have a sheet that I use for production numbers at work

There are 3 columns of 37 rows
I want to be able to find the second non blank cell in each column and
then
get an average from the sum of the second cell to 37th cell in that column
and be able to do this for each column.
However I am not able to figue out how to do this as the second non blank
cell in each column may be different. ( Column one the second cell may be
in
row 4 and in Column 2 the second cell may be in row 2 and in Column 3 the
secind cell may be in row 6) The reason that I want the second cell is
that
the first cell of production numbers are never used to calculate average
production.
Please help
Thanks

Dan N
--
cruchnin numbers

Identifying first non blank cell in 3 different columns
 

Hi Thanks for the reply

My Cell range will be from A3 to A37. sorry for leaving that out
but I put those cell values in and it gave me a #ref error
not sure why???
--
crunchnin numbers


"T. Valko" wrote:

Try this:

=AVERAGE(A37:INDEX(A1:A37,MAX(INDEX((A1:A37="")*(R OW(A1:A37)),,))+1))

Copy across

I'm assuming that the 2nd blank cell will never be the last cell in the
range!!!!!!

Biff

"belvy123" wrote in message
...
I have a sheet that I use for production numbers at work

There are 3 columns of 37 rows
I want to be able to find the second non blank cell in each column and
then
get an average from the sum of the second cell to 37th cell in that column
and be able to do this for each column.
However I am not able to figue out how to do this as the second non blank
cell in each column may be different. ( Column one the second cell may be
in
row 4 and in Column 2 the second cell may be in row 2 and in Column 3 the
secind cell may be in row 6) The reason that I want the second cell is
that
the first cell of production numbers are never used to calculate average
production.
Please help
Thanks

Dan N
--
cruchnin numbers

Identifying first non blank cell in 3 different columns
 

Ooops! Disregard that. I misread. I read it to mean that you're looking for
the 2 blank cell.

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(A37:INDEX(A1:A37,SMALL(IF(A1:A37<"",ROW( A1:A37)-MIN(ROW(A1:A37))+1),2)))

Copy across

I'm assuming there are at least 2 non-blank cells in the range.

Biff

"T. Valko" wrote in message
...
Try this:

=AVERAGE(A37:INDEX(A1:A37,MAX(INDEX((A1:A37="")*(R OW(A1:A37)),,))+1))

Copy across

I'm assuming that the 2nd blank cell will never be the last cell in the
range!!!!!!

Biff

"belvy123" wrote in message
...
I have a sheet that I use for production numbers at work

There are 3 columns of 37 rows
I want to be able to find the second non blank cell in each column and
then
get an average from the sum of the second cell to 37th cell in that
column
and be able to do this for each column.
However I am not able to figue out how to do this as the second non blank
cell in each column may be different. ( Column one the second cell may be
in
row 4 and in Column 2 the second cell may be in row 2 and in Column 3 the
secind cell may be in row 6) The reason that I want the second cell is
that
the first cell of production numbers are never used to calculate average
production.
Please help
Thanks

Dan N
--
cruchnin numbers

Identifying first non blank cell in 3 different columns
 

Hi Valko

You are my new hero
That formula gave me a headache to read but man it works great
Thanks Again

DN
--
crunchnin numbers


"T. Valko" wrote:

Ooops! Disregard that. I misread. I read it to mean that you're looking for
the 2 blank cell.

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(A37:INDEX(A1:A37,SMALL(IF(A1:A37<"",ROW( A1:A37)-MIN(ROW(A1:A37))+1),2)))

Copy across

I'm assuming there are at least 2 non-blank cells in the range.

Biff

"T. Valko" wrote in message
...
Try this:

=AVERAGE(A37:INDEX(A1:A37,MAX(INDEX((A1:A37="")*(R OW(A1:A37)),,))+1))

Copy across

I'm assuming that the 2nd blank cell will never be the last cell in the
range!!!!!!

Biff

"belvy123" wrote in message
...
I have a sheet that I use for production numbers at work

There are 3 columns of 37 rows
I want to be able to find the second non blank cell in each column and
then
get an average from the sum of the second cell to 37th cell in that
column
and be able to do this for each column.
However I am not able to figue out how to do this as the second non blank
cell in each column may be different. ( Column one the second cell may be
in
row 4 and in Column 2 the second cell may be in row 2 and in Column 3 the
secind cell may be in row 6) The reason that I want the second cell is
that
the first cell of production numbers are never used to calculate average
production.
Please help
Thanks

Dan N
--
cruchnin numbers

Identifying first non blank cell in 3 different columns
 

You're welcome. Thanks for the feedback!

Biff

"belvy123" wrote in message
...
Hi Valko

You are my new hero
That formula gave me a headache to read but man it works great
Thanks Again

DN
--
crunchnin numbers


"T. Valko" wrote:

Ooops! Disregard that. I misread. I read it to mean that you're looking
for
the 2 blank cell.

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(A37:INDEX(A1:A37,SMALL(IF(A1:A37<"",ROW( A1:A37)-MIN(ROW(A1:A37))+1),2)))

Copy across

I'm assuming there are at least 2 non-blank cells in the range.

Biff

"T. Valko" wrote in message
...
Try this:

=AVERAGE(A37:INDEX(A1:A37,MAX(INDEX((A1:A37="")*(R OW(A1:A37)),,))+1))

Copy across

I'm assuming that the 2nd blank cell will never be the last cell in the
range!!!!!!

Biff

"belvy123" wrote in message
...
I have a sheet that I use for production numbers at work

There are 3 columns of 37 rows
I want to be able to find the second non blank cell in each column and
then
get an average from the sum of the second cell to 37th cell in that
column
and be able to do this for each column.
However I am not able to figue out how to do this as the second non
blank
cell in each column may be different. ( Column one the second cell may
be
in
row 4 and in Column 2 the second cell may be in row 2 and in Column 3
the
secind cell may be in row 6) The reason that I want the second cell is
that
the first cell of production numbers are never used to calculate
average
production.
Please help
Thanks

Dan N
--
cruchnin numbers