Morning everyone
I have a data set of numbers and I would like to write a formula which selects the range that the value falls into.
i.e. one of my numbers in the data set is 100. My range values are 0-50, 51-100, 101-150, etc, and 0-50 corresponds to "set 1", 51-100 to "set 2", 101-150 to "set 3".
I'd like my formula to return "set 2".
I can write a cumbersome IF formula but there is an easier way - can anyone please assist?
Thanks very much
Try this:
Formula:
Please Login or Register to view this content.
Last edited by zbor; 09-15-2014 at 05:20 AM.
Hi,
Create a small two column table with your set limits, 0, 51, 101 etc in column 1 and 'set 1', 'set 2' etc.. in the adjacent column.
Now use a VLOOKUP() function. e.g.
Formula:Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
=LOOKUP(A1,{0,51,101},{1,2,3})
extend the numbers in {} if there are more sets
or
=INT((A1-1)/50)+1
will produce the correct result irrespective of how many sets there are
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
excellent, thanks all for the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks