Look up for a value in a s et of ranges

[a_sahay]

Hi ,

I want to locate values in a set of range. For eg if i have observations
from 1 to 100 and in another column ranges like 1-5, 6-10 etc. I want a
function which can help me to put each number in the best fit range. How can
i do it. The set of 1-100 may not be in a ascending /descending order.

Rgds

 

[John C]

Assuming your observations are whole numbers, and you want in groups of 5
ranges as indicated, you could use this formula:
=(ROUNDUP(A2/5,0)-1)*5+1&" - "&(ROUNDUP(A2/5,0)-1)*5+5

Where A2 is your observation number, copy down as needed.