Microsoft KB Archive/101167: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
| Line 57: | Line 57: | ||
To find the nth nonblank value in a range, enter the following formula as an array: | To find the nth nonblank value in a range, enter the following formula as an array: | ||
<pre class="codesample"> =INDEX(range,SMALL(IF(ISBLANK(range), | <pre class="codesample"> =INDEX(range,SMALL(IF(ISBLANK(range),"",ROW(range)),n)-ROW(range)+1) | ||
</pre> | </pre> | ||
NOTE: To enter the formula as an array, type the formula in a cell and press CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+ENTER in Microsoft Excel for the Macintosh.<br /> | NOTE: To enter the formula as an array, type the formula in a cell and press CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+ENTER in Microsoft Excel for the Macintosh.<br /> | ||
| Line 66: | Line 66: | ||
<br /> | <br /> | ||
In the following section of the formula, | In the following section of the formula, | ||
<pre class="codesample"> SMALL(IF(ISBLANK(range), | <pre class="codesample"> SMALL(IF(ISBLANK(range),"",ROW(range)),n) | ||
</pre> | </pre> | ||
the IF() formula returns an array of row numbers where the condition is met. In this example, an array of row numbers for all the nonblank cells is returned by the IF() function. The SMALL() function looks at these row numbers and returns the nth smallest row number.<br /> | the IF() formula returns an array of row numbers where the condition is met. In this example, an array of row numbers for all the nonblank cells is returned by the IF() function. The SMALL() function looks at these row numbers and returns the nth smallest row number.<br /> | ||
<br /> | <br /> | ||
In the -ROW(range)+1 section of the formula, the starting row number in the range is subtracted from the row number returned by SMALL() and then 1 is added. This calculates a relative | In the -ROW(range)+1 section of the formula, the starting row number in the range is subtracted from the row number returned by SMALL() and then 1 is added. This calculates a relative "position" of the value in the range so that the value can be returned with the INDEX() function.<br /> | ||
<br /> | <br /> | ||
If the nth value is a blank, and the remaining cells in the range are blank, the #NUM! error value will be returned to the cell. If you want to find the nth value from the bottom up (instead of from the top down), use the LARGE() function instead of the SMALL() function. | If the nth value is a blank, and the remaining cells in the range are blank, the #NUM! error value will be returned to the cell. If you want to find the nth value from the bottom up (instead of from the top down), use the LARGE() function instead of the SMALL() function. | ||
| Line 79: | Line 79: | ||
== REFERENCES == | == REFERENCES == | ||
"Function Reference", version 4.0, pages 236-238, 250-251, 406 | |||
</div> | </div> | ||
Latest revision as of 09:16, 20 July 2020
Article ID: 101167
Article Last Modified on 11/25/2003
APPLIES TO
- Microsoft Excel 4.0 Standard Edition
- Microsoft Excel 4.0a
- Microsoft Excel 4.0 for Macintosh
- Microsoft Excel 97 Standard Edition
This article was previously published under Q101167
SUMMARY
In Microsoft Excel 4.0, to find the nth value in a range of cells that meets a condition, use the SMALL() or LARGE() function to evaluate the array of row numbers that meet the condition.
MORE INFORMATION
Example
To find the nth nonblank value in a range, enter the following formula as an array:
=INDEX(range,SMALL(IF(ISBLANK(range),"",ROW(range)),n)-ROW(range)+1)
NOTE: To enter the formula as an array, type the formula in a cell and press CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+ENTER in Microsoft Excel for the Macintosh.
In above example, the range argument refers to the cells you are searching and the n argument is a number indicating the occurrence you are looking for. For example, if range refers to cells A1:A10 and n is 2, the formula returns the second nonblank value from cells A1:A10.
Following is a description of how the formula works:
In the following section of the formula,
SMALL(IF(ISBLANK(range),"",ROW(range)),n)
the IF() formula returns an array of row numbers where the condition is met. In this example, an array of row numbers for all the nonblank cells is returned by the IF() function. The SMALL() function looks at these row numbers and returns the nth smallest row number.
In the -ROW(range)+1 section of the formula, the starting row number in the range is subtracted from the row number returned by SMALL() and then 1 is added. This calculates a relative "position" of the value in the range so that the value can be returned with the INDEX() function.
If the nth value is a blank, and the remaining cells in the range are blank, the #NUM! error value will be returned to the cell. If you want to find the nth value from the bottom up (instead of from the top down), use the LARGE() function instead of the SMALL() function.
REFERENCES
"Function Reference", version 4.0, pages 236-238, 250-251, 406
Additional query words: 4.00a howto returning
Keywords: KB101167
