BlosMusic
asked on
Finding next highest number in a list against a number in a cell
I have a list of numbers. I also have a cell that generates a number from elsewhere. I want to select the next highest number above the number in the cell, from my list.
Can someone assist? I cannot seem to find an easy solution.
Can someone assist? I cannot seem to find an easy solution.
Eirman
In principle, create a new list of numbers which would be your existing list less your test number. Then use the MIN function on the new list.
So the desired result is a number larger than the one in the cell? In particular, the smallest such number?
If you have Excel 2010 or later, you can use the AGGREGATE function in a formula like:
=AGGREGATE(15,6,A1:A23/(A1
In the above formula, A1:A23 is the list of numbers and D4 is the cell that generates a number from elsewhere.
If you have Excel 2010 or later, you can use the AGGREGATE function in a formula like:
=AGGREGATE(15,6,A1:A23/(A1
In the above formula, A1:A23 is the list of numbers and D4 is the cell that generates a number from elsewhere.
Going back to my original suggestion, If your new list is in B1:B50 your answer would be
=MIN(B1:B50)
=MIN(B1:B50)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=LARGE(A1:A500, 5)
returns to 5th largest value in range selected
returns to 5th largest value in range selected
ooopss to many posts sorry
ASKER
Hey everyone - what a quick response! I will go through this later - brilliant ideas out there!
Thanks. Will check back later.
Thanks. Will check back later.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Barry,
That works perfectly! Now I have to educate myself and find out why!
Many thanks all.
That works perfectly! Now I have to educate myself and find out why!
Many thanks all.