Ask Your Question

How to detect threshold within a cell range [closed]

asked 2017-02-24 14:08:04 +0200

moi1 gravatar image


I'd like to detect where a value reaches a threshold within a range of cells. When the threshold is reached, I want to read a cell above. My table contains a %age that grows up to 100%. I'd like to find when it reaches 70% and read the cell just above the >70% value.

Couldn't find any smart function to do this...

Thanks! Laurent

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by moi1
close date 2017-02-24 16:05:39.814199

1 Answer

Sort by » oldest newest most voted

answered 2017-02-24 15:08:46 +0200

karolus gravatar image


=INDEX( A1:A999 ; MATCH( 0.7 ; A1:A99 ;0 )-1)

in case the entries are in ascending order, use:

=INDEX( A1:A999 ; MATCH( 0.7 ; A1:A99 )-1)

edit flag offensive delete link more


Excellent, thanks!

moi1 gravatar imagemoi1 ( 2017-02-24 16:05:19 +0200 )edit

Question Tools

1 follower


Asked: 2017-02-24 14:08:04 +0200

Seen: 203 times

Last updated: Feb 24 '17