find where a number belongs in a series of numbers

  • Thread starter Thread starter greg
  • Start date Start date
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
 
Hi

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

Hopes this helps.
....
Per
 
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".
 
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
 
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...
 
Back
Top