Closest in Range to 100%

[Jamsandwich]

Hey guys,

I am using this array formula to get the closest number, in a range, to 100% -

=INDEX(B76:B79,MATCH(MIN(ABS(B76:B79-B74)),ABS(B76:B79-B74),0))

It works great. I was wondering if it is possible to make sure that it only returns values that are under 100%.

 

[Learning To Excel]

Definitely more than one way to do it. Give this a try, also array formula:

=MAX(B76:B83-B74*INT(B76:B83/B74))

If you wanted a condition for no values under 100% you could add something like this:

=IF(MAX(B76:B83-B74*INT(B76:B83/B74))>0,MAX(B76:B83-B74*INT(B76:B83/B74)),"No values under 100%")

 

[pgc01]

Hi

If I understand correctry you want the number closest but less than B74.

If this is correct, try this array formula:

=MAX(IF(B76:B79<B74,B76:B79))<b74,b76:b79))< html=""></b74,b76:b79))<>

 

[Jamsandwich]

Thanks guys.

PGCs solution appears to have worked. If anything goes wrong I can try LearningToExcel's.

Problem solved!

 

[pgc01]

I'm glad it helped. Thanks for the feedbakc.