find where a number belongs in a series of numbers

G

greg

Is there an easy excel formula that I can use that will find where a number
belongs in a series of numbers.
So for instance
in A1 to A 500 I have random numbers. I can order them lowest to highest.
then I have a new single number.
Lets say it would go between 40 and 41
is there a excel formula that would help me on this?
to make it easy and the right way to do it.
instead of a lot of code?

thanks
 
P

Per Jessen

Hi

Why not put the new number in the first empty cell in the column, then
sort the column.

Hopes this helps.
....
Per
 
R

Rick Rothstein

Are you looking for the row number at which you would insert the entry at?
If so, and assuming there are no repeated values in the random list...

InsertAtRow = 1 + WorksheetFunction.Match(N, Range("A1:A500"), 1)

where N is the "new single number".
 
P

Patrick Molloy

the MATCH function will do that

=MATCH(D3,A1:A500,TRUE)

as your numbers are sorted, TRUE will return index the number 'nearest' to
the one you're looking for.

so if in A you had 39 40 41 42 43 and you did a match on 41.4 then answer
would be 3 .... as the 3rd item, 41 is the closest to, and less than your
query
 
G

greg

thanks all
match works


Patrick Molloy said:
the MATCH function will do that

=MATCH(D3,A1:A500,TRUE)

as your numbers are sorted, TRUE will return index the number 'nearest' to
the one you're looking for.

so if in A you had 39 40 41 42 43 and you did a match on 41.4 then answer
would be 3 .... as the 3rd item, 41 is the closest to, and less than your
query
Click to expand...
 
Top