find where a number belongs in a series of numbers
 

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

find where a number belongs in a series of numbers
 

Hi

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

Hopes this helps.
....
Per


On 15 Jul., 18:12, "greg" wrote:
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

find where a number belongs in a series of numbers
 

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".

--
Rick (MVP - Excel)


"greg" wrote in message
...
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

find where a number belongs in a series of numbers
 

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


"greg" wrote in message
...
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

find where a number belongs in a series of numbers
 

thanks all
match works


"Patrick Molloy" wrote in message
...
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


"greg" wrote in message
...
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