Solved

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

Posted on 2013-02-02
5
337 Views
Last Modified: 2013-02-02
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.
0
Comment
Question by:Cactus1994
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38847446
The formula below should do the job:

=SMALL(A1:A100,COUNTIF(A1:A100,"<=1000"))
0
 

Author Comment

by:Cactus1994
ID: 38847472
What happens in the event all my numbers are ABOVE 1,000? It doesn't seem to work. Thx.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38847487
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38847607
>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
 

Author Closing Comment

by:Cactus1994
ID: 38847625
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now