Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the last number in a series | Excel Worksheet Functions | |||
How do I use VBA to find which named ranges a cell belongs to? | Excel Programming | |||
How to find missing numbers in series | Excel Worksheet Functions | |||
how can we find out any missed number in a series? | Excel Discussion (Misc queries) | |||
Given a cell, find the named range it belongs to | Excel Programming |