So I have a small database with measurements. I need to rank each measurement on a scale from 1-10. The scale goes as follows:
0 10
0-1 9
1-2 8
2-4 7
4-8 6
8-12 5
12-16 4
16-24 3
24-32 2
32-40 1
40 0
How do I make a function to place rank each measurement???
Thanks in advance!!
Sorry it looks like my CnP didnt work well from my table.
Rank is 10 to 0 (on the right)
And Measurement 0 to 40 (on the left)
Hi and welcome to the forum
I would suggest you create a small table with the low end in the 1st column and the "rank" in the 2nd column...you could then use a vlookup to pull in the rank. However, each of your levels overlap. For instance if the measurement is, say, 8, would that rank as 6 or 5?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
With this list in D1:E10
and
Please Login or Register to view this content.
A1: a value to rank...0.1
This regular formula assigns the rank
In that example, the formula returns: 9Please Login or Register to view this content.
Is that something you can work with?
It appears that you have overlapping measurements. If I am understanding correctly 0 to 1 gets a 9 ranking while 1 to 2 gets an 8. So a 1 could have either a 9 or an 8 ranking. Please clarify.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
All measurements are recorded to the hundredth. Also they are 4 measurements averaged together to make the recorded value. The probability that a value is exactly even integer is extremely low so it is not a concern but I would pick a rule and keep it consistant...ie 1.00 is 9 and and 2.00 is 8
Rons INDEX/MATCH should work, and so will a regular vlookup
This worked!!! Thanks Ron. Sorry it took awhile to get back to this but this was the ticket
Originally Posted by Ron Coderre
With this list in D1:E10
andPlease Login or Register to view this content.
A1: a value to rank...0.1
This regular formula assigns the rank
In that example, the formula returns: 9Please Login or Register to view this content.
Is that something you can work with?
Glad you got something you can use!
Be sure to mark this thread as SOLVED (from the Thread Tools menu)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks