Finding the highest value in a range that falls below a set target

N

Neil

Hi,

I have a row of data as follows:
0.8
23.7
42.0
56.4
67.7
76.4
83.0
87.9
91.5
94.2
96.0
97.4
98.3
98.9
99.3
99.6
99.7
99.8

If the target was 80, i want to be able to return the value that falls
just below it. In the above example it would be 76.4. On the flip side
i also want to return the value that is just above the target (in a
separate cell). In the above case it would be 83.

I figure that the formula would need to incorporate the MIN & MAX
functions, but just can't get me head around it. Alternatively if
anyone knows of any different formula/s i could use, it would be good.

Any help would be greatly appreciated.

Thanks
Neil
 
M

Max

One way which might suffice ..

Assuming data in A1 down, sorted in ascending order

Target value entered in C1: 80

Then in D1:
=INDEX(A:A,MATCH(C1,A:A))
returns 76.4

In E1:
=INDEX(A:A,MATCH(C1,A:A)+1)
returns 83
 
N

Neil

One way which might suffice ..

Assuming data in A1 down, sorted in ascending order

Target value entered in C1: 80

Then in D1:
=INDEX(A:A,MATCH(C1,A:A))
returns 76.4

In E1:
=INDEX(A:A,MATCH(C1,A:A)+1)
returns 83
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik










- Show quoted text -
Click to expand...

Thanks Max

This works sort of. Unfortunately if a particular value in the range
of data is the same as the target - then this formula
[=INDEX(A:A,MATCH(C1,A:A))] simply returns the same value. I could
change the formula to =INDEX(A:A,MATCH(C1,A:A)-1) but the problem will
be that it will return two values below if there is no exact match for
the target.

Do you have any further suggestions i could possibly try?

Thanks
Neil
 
M

Max

Thanks Max

This works sort of. Unfortunately if a particular value in the range
of data is the same as the target - then this formula
[=INDEX(A:A,MATCH(C1,A:A))] simply returns the same value. I could
change the formula to =INDEX(A:A,MATCH(C1,A:A)-1) but the problem will
be that it will return two values below if there is no exact match for
the target. Do you have any further suggestions i could possibly try?
Click to expand...

Ahh .. Then these modifications to the earlier set of formulas should
do the job ..

Source data in A1 down (assumed in ascending sort),
Target value entered in C1

Return value just above:
=IF(ISNUMBER(MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,
0)-1),INDEX(A:A,MATCH(C1,A:A)))

Return value just below:
=IF(ISNUMBER(MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,
0)+1),INDEX(A:A,MATCH(C1,A:A)+1))
 
N

Neil

Thanks Max
Click to expand...
This works sort of. Unfortunately if a particular value in the range
of data is the same as the target - then this formula
[=INDEX(A:A,MATCH(C1,A:A))] simply returns the same value. I could
change the formula to =INDEX(A:A,MATCH(C1,A:A)-1) but the problem will
be that it will return two values below if there is no exact match for
the target. Do you have any further suggestions i could possibly try?
Click to expand...

Ahh .. Then these modifications to the earlier set of formulas should
do the job ..

Source data in A1 down (assumed in ascending sort),
Target value entered in C1

Return value just above:
=IF(ISNUMBER(MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,
0)-1),INDEX(A:A,MATCH(C1,A:A)))

Return value just below:
=IF(ISNUMBER(MATCH(C1,A:A,0)),INDEX(A:A,MATCH(C1,A:A,
0)+1),INDEX(A:A,MATCH(C1,A:A)+1))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
Click to expand...

Thanks Max - works perfectly! :)
 
B

Bernd

Hello Neil,

Another approach (array-entered) if your input value is in C1:
=C1-MIN(IF(C1>A1:A18,C1-A1:A18))
=C1-MAX(IF(C1<A1:A18,C1-A1:A18))

Regards,
Bernd
 
Top