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)

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

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

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