Excel formula to return the lowest, closest number to another?

I have a column of numbers between 1 and 5,000, in cells A1:A100

How do I write an Excel formula that returns the lowest, closest number to the number 1,000?

(For example, if the closest numbers to 1,000 are the numbers 900 and 1,100 -- it should return the number 900.)

Thanks.
Tim JackoboiceOwnerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
barry houdiniConnect With a Mentor Commented:
>How do I write an Excel formula that returns the lowest, closest number to the number 1,000?

Try this formula

=MIN(IF(ABS(A1:A100-1000)=MIN(ABS(A1:A100-1000)),A1:A100))

confirmed with CTRL+SHIFT+ENTER

If you have 999 and 1001 in the list the formula returns 999

regards, barry
0
 
SteveCommented:
The formula below should do the job:

=SMALL(A1:A100,COUNTIF(A1:A100,"<=1000"))
0
 
Tim JackoboiceOwnerAuthor Commented:
What happens in the event all my numbers are ABOVE 1,000? It doesn't seem to work. Thx.
0
 
SteveCommented:
The following will return "ALL GREATER" if there is not a number lower than  the chosen:
=IFERROR(SMALL(A1:A100,COUNTIF(A1:A100,"<=1000")),"ALL GREATER")
You can swap the "ALL GREATER" for whatever reponse suits.
0
 
Tim JackoboiceOwnerAuthor Commented:
Hi Barry: Perfect. That works. Thanks to you, too, Barman, but I couldn't use a text filler.

Barry, would you mind taking a look at my other question, "Excel: Formula to Return an Answer Closest to a Target", too?

Seems like we were getting close and then the answers dropped off. Thanks!
0
All Courses

From novice to tech pro — start learning today.