Find first threshold violation

N

Nick Krill

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)
 
J

John Michl

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$1>A1:A8,"",A1:A8-$G$1))+$G$1,A1:A8)

- John
www.JohnMichl.com
 
S

Sandy Mann

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:A8>G1,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
[email protected]
[email protected] with @tiscali.co.uk
 
R

Ron Rosenfeld

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)
Click to expand...

If rng is your range containing your unsorted data, and TH is the threshold
amount, then the **array** formula:

=MATCH(TRUE,rng>TH,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
 
Top