Good day.
I have have been pounding my head against the wall trying to determine a way to have certain ranges recognized and a value created from the range. An example for what I am trying to acheive would be a formula to create the results in column C:
Column A Column C
Volume Rating
22,222,222 4
26,666,666 5
12,000,000 3
8,000,000 2
4,000,000 1
The Key I am using for the rating is:
25,000,000 < = 5
14,000,000-24,999,999 =4
10,000,000-13,999,999 = 3
6,000,000 - 9,999,999 = 2
5,999,999 > = 1
So anything within a certain range would have a rating created by the formula in column C. Any help would be GREATLY APPRECIATED!!
Hi,
Does
help?=LOOKUP(A1,{0,6000000,10000000,14000000,25000000},{1,2,3,4,5})
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
I'd recommend creating a 2 column table. In the first column, put the larger of the 2 numbers (I.E. 25000, 14000 etc.) Put the value you want to return in the second column (ie 5, next cell 4, etc) Name the table Lookups or something similar. Then use this formula (assuming you have your values you're testing in the A column)=VLOOKUP(A1,Lookups,2,TRUE)
If your question has been satisfactorily addressed, please consider marking it solved. Click here to see how.
Also, you might want to add to the user's reputation by clicking the scales icon - it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks