Formula needed

[baggarwal]

Hi All:

I have two columns one with the marks and the other with a pass rate. Let say it is
30 94.1%
34 95.4%
38 97%

I need a formula that will look for the value closest to 95%. In this case the number I want the formula to provide is 34 because the closest variation to 95% is 95.4% so I am just looking for the closest match. Can someone tell me how to develop this formula?

 

[Jonmo1]

Do you want the closest without going over?

Example, what would you want returned for say 96%, 34 or 38?

 

[baggarwal]

Yes sorry I meant to say the closest to 95% without going over.

 

[Jonmo1]

So for an input of 96%, you would want 38 returned based on the table you provided?

Do you have this table written in a range somewhere, can it be sorted DESCENDING by the percentages?

 

[Aladin Akyurek]

Hi All:

I have two columns one with the marks and the other with a pass rate. Let say it is
30 94.1%
34 95.4%
38 97%

I need a formula that will look for the value closest to 95%. In this case the number I want the formula to provide is 34 because the closest variation to 95% is 95.4% so I am just looking for the closest match. Can someone tell me how to develop this formula?

Let A2:B4 house the figures you posted and E2 95%, the value of interest.

Control+shift+enter, not just enter:

=INDEX(A2:A4,MATCH(MIN(ABS(B2:B4-E2)),ABS(B2:B4-E2),0))

 

[T. Valko]

Hi All:

I have two columns one with the marks and the other with a pass rate. Let say it is
30 94.1%
34 95.4%
38 97%

I need a formula that will look for the value closest to 95%. In this case the number I want the formula to provide is 34 because the closest variation to 95% is 95.4% so I am just looking for the closest match. Can someone tell me how to develop this formula?

Yes sorry I meant to say the closest to 95% without going over.

If you want the closest without going over then the result should be 30 not 34.

Array entered**:

=INDEX(A2:A4,MATCH(MAX(IF(B2:B4<=0.95,B2:B4)),B2:B4,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Jonmo1]

If you want the closest without going over then the result should be 30 not 34.

I guess that depends on perspective..
Which value is going OVER, the input value, or the vector value...

The input value of 95% IS over 94.1 %, therefor we do not want 30
The input value of 95% IS NOT over 95.4% therfore we DO want 34.


I was thinking in terms of either a -1 or +1 match type using

=INDEX(A2:A4,MATCH(E2,B2:B4,-1))

Where:
A2:B4 = the table given by OP sorted DESCENDING on column B.
E2 = input value of 95%

Excel Workbook
	A	B	C	D	E
2	38	97.00%			95.00%
3	34	95.40%
4	30	94.10%			34
Sheet1

 

[T. Valko]

I guess that depends on perspective..

The input value of 95% IS over 94.1 %, therefor we do not want 30
The input value of 95% IS NOT over 95.4% therfore we DO want 34.

Yes sorry I meant to say the closest to 95% without going over.

The closest to 95% without going over is 94.1% which corresponds to 30.