|
Finding the position of the i-th non blank cell in a vertical range
Understanding what I need is easy; finding the correct formula perhaps
not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 (blank) 2 (blank) 3 AB 4 (blank) 5 ZM 6 (blank) 7 HJ .... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply |
Finding the position of the i-th non blank cell in a vertical range
Try this array formula** :
=IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B11: B100,SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)-ROW(B11)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... Understanding what I need is easy; finding the correct formula perhaps not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 (blank) 2 (blank) 3 AB 4 (blank) 5 ZM 6 (blank) 7 HJ ... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply |
Finding the position of the i-th non blank cell in a vertical
Biff - You can simplify that a little
=IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B:B, SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)))) If the OP isn't concerned about checking the boundaries, then =INDEX(B:B,SMALL(IF(C11:C100<"",ROW(B11:B100)),M1 1)) "T. Valko" wrote: Try this array formula** : =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B11: B100,SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)-ROW(B11)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... Understanding what I need is easy; finding the correct formula perhaps not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 (blank) 2 (blank) 3 AB 4 (blank) 5 ZM 6 (blank) 7 HJ ... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply |
Finding the position of the i-th non blank cell in a vertical
I'm not sure how INDEX is handled in memory.
Does it index just the used range or does it index the referenced range? If it indexes the specific referenced range and the range is only a hundred rows or so, using B:B would seem to be inefficient so I prefer using specific ranges. On a related note to using these types of formulas, I've discovered a more efficient way to calculate the offset. The "standard method" was like this: ROW(B11:B100)-ROW(B11)+1 Or, the more robust "user-proof" : ROW(B11:B100)-MIN(ROW(B11:B100))+1 These are processed as an array. Moving the offset adjustment to the SMALL function eliminates that much of the array processing: SMALL(.......))-ROW(B11)+1 SMALL(.......))-MIN(ROW(B11:B100))+1 -- Biff Microsoft Excel MVP "Duke Carey" wrote in message ... Biff - You can simplify that a little =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B:B, SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)))) If the OP isn't concerned about checking the boundaries, then =INDEX(B:B,SMALL(IF(C11:C100<"",ROW(B11:B100)),M1 1)) "T. Valko" wrote: Try this array formula** : =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B11: B100,SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)-ROW(B11)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... Understanding what I need is easy; finding the correct formula perhaps not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 (blank) 2 (blank) 3 AB 4 (blank) 5 ZM 6 (blank) 7 HJ ... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply |
Finding the position of the i-th non blank cell in a vertical
On big models your concern is justified. In a small one, probably not much
of a difference. Your second point is VERY interesting. "T. Valko" wrote: I'm not sure how INDEX is handled in memory. Does it index just the used range or does it index the referenced range? If it indexes the specific referenced range and the range is only a hundred rows or so, using B:B would seem to be inefficient so I prefer using specific ranges. On a related note to using these types of formulas, I've discovered a more efficient way to calculate the offset. The "standard method" was like this: ROW(B11:B100)-ROW(B11)+1 Or, the more robust "user-proof" : ROW(B11:B100)-MIN(ROW(B11:B100))+1 These are processed as an array. Moving the offset adjustment to the SMALL function eliminates that much of the array processing: SMALL(.......))-ROW(B11)+1 SMALL(.......))-MIN(ROW(B11:B100))+1 -- Biff Microsoft Excel MVP "Duke Carey" wrote in message ... Biff - You can simplify that a little =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B:B, SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)))) If the OP isn't concerned about checking the boundaries, then =INDEX(B:B,SMALL(IF(C11:C100<"",ROW(B11:B100)),M1 1)) "T. Valko" wrote: Try this array formula** : =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B11: B100,SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)-ROW(B11)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... Understanding what I need is easy; finding the correct formula perhaps not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 (blank) 2 (blank) 3 AB 4 (blank) 5 ZM 6 (blank) 7 HJ ... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply |
Finding the position of the i-th non blank cell in a vertical
On 10 sep, 22:33, Duke Carey
wrote: On big models your concern is justified. *In a small one, probably not much of a difference. Your second point is VERY interesting. "T. Valko" wrote: I'm not sure how INDEX is handled in memory. Does it index just the used range or does it index the referenced range? If it indexes the specific referenced range and the range is only a hundred rows or so, using B:B would seem to be inefficient so I prefer using specific ranges. On a related note to using these types of formulas, I've discovered a more efficient way to calculate the offset. The "standard method" was like this: ROW(B11:B100)-ROW(B11)+1 Or, the more robust "user-proof" : ROW(B11:B100)-MIN(ROW(B11:B100))+1 These are processed as an array. Moving the offset adjustment to the SMALL function eliminates that much of the array processing: SMALL(.......))-ROW(B11)+1 SMALL(.......))-MIN(ROW(B11:B100))+1 -- Biff Microsoft Excel MVP "Duke Carey" wrote in message ... Biff - You can simplify that a little =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B:B, SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)))) If the OP isn't concerned about checking the boundaries, then =INDEX(B:B,SMALL(IF(C11:C100<"",ROW(B11:B100)),M1 1)) "T. Valko" wrote: Try this array formula** : =IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B11: B100,SMALL(IF(C11:C100<"",ROW(B11:B100)),M11)-ROW(B11)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "vsoler" wrote in message ... Understanding what I need is easy; finding the correct formula perhaps not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3.... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 *(blank) 2 *(blank) 3 *AB 4 *(blank) 5 *ZM 6 *(blank) 7 *HJ ... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply Your answers are great!!! I was about to conclude that such a complex formula was not possible. Thank you very much. |
Finding the position of the i-th non blank cell in a vertical range
Hi,
In cell D11, enter the following formula = IF(COUNTBLANK(C11)=1,"",COUNTA($C$11:C11)). Copy this formula down to cell C100. Also, give a heading to this new column - in cell D10, type Revised No. In range C102:D102 type Revised No. and the heading of the column C. In C103, type 3 and in D103, use the DGET() formula. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "vsoler" wrote in message ... Understanding what I need is easy; finding the correct formula perhaps not so much Say that my range, in C11:C100 , contains some cells that are not empty (non blank). Say that in B11:B100 I have the series 1, 2, 3... and so on until 90. Imagine that the first cells in my range B11:C100 contain 1 (blank) 2 (blank) 3 AB 4 (blank) 5 ZM 6 (blank) 7 HJ ... Now, if in cell M11 I input the value 3, that means that I need to find the 3rd non blank cell in my C column range. That is, my function should return 7. I can work with array funsctions, but I would not like to work with intermediate calculations in additional cells. Is it impossible what I am looking for? Thank you for any help that you may supply |
| All times are GMT +1. The time now is 07:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com