| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| identifying the page a named cell is on | Excel Discussion (Misc queries) | |||
| Identifying a cell/value in a table | Excel Discussion (Misc queries) | |||
| Identifying Duplicates in Columns | Excel Worksheet Functions | |||
| How do I make a blank cell with a date format blank? | Excel Worksheet Functions | |||
| COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
Linear Mode
