Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first threshold violation
How can I find the location of the first incidence of a threshold amount
being exceeded in a row of unsorted data: threshold: 45.52 44.63, 45.27, 44.98, 45.61, 45.25, 44.87, 45.63, 45.45 the answer would be 4 (45.61) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first threshold violation
Assuming your values are in cell A1 through A8 and the threshold is in
G1, try the following array formula which will return the place in the array (in your example, 4). Commit the formula by pressing Ctrl-Shift-Enter. If you want the value (45.61) instead of the position, remove the Match( and the ,A1:A8) at the tail end of the formula. =MATCH(MIN(IF($G$1A1:A8,"",A1:A8-$G$1))+$G$1,A1:A8) - John www.JohnMichl.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first threshold violation
John,
MATCH returns inconsistent results in unsorted lists unless you give it a zero third argument whereupon it returns exact Matches only. Your formula returns 1 for an entry of 44.7 in G1 where 2 would be the correct return and returns 4 for 45.61 - the actual position of the value not where it is exceeded. Also it returns 8 for any G1 bigger than the largest value in the list although I assume you intended it the return "" I came up with: =MIN(IF(A1:A8G1,ROW(A1:A8))) again array entered with Ctrl + Shift + Enter. It has has not been exhaustively tested but seem to work with the figures that I have plugged in (but then I assume your's did as well<g). It returns Zero for G1 values larger than the values in the list -- Regards Sandy with @tiscali.co.uk "John Michl" wrote in message ups.com... Assuming your values are in cell A1 through A8 and the threshold is in G1, try the following array formula which will return the place in the array (in your example, 4). Commit the formula by pressing Ctrl-Shift-Enter. If you want the value (45.61) instead of the position, remove the Match( and the ,A1:A8) at the tail end of the formula. =MATCH(MIN(IF($G$1A1:A8,"",A1:A8-$G$1))+$G$1,A1:A8) - John www.JohnMichl.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first threshold violation
On Wed, 4 Jan 2006 11:59:02 -0800, "Nick Krill"
wrote: How can I find the location of the first incidence of a threshold amount being exceeded in a row of unsorted data: threshold: 45.52 44.63, 45.27, 44.98, 45.61, 45.25, 44.87, 45.63, 45.45 the answer would be 4 (45.61) If rng is your range containing your unsorted data, and TH is the threshold amount, then the **array** formula: =MATCH(TRUE,rngTH,0) will give you the first incidence where the range exceeds the threshold amount. To enter an **array** function, after typing the function, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace | Excel Worksheet Functions | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
How do I find a column entry closest to a particular value | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |