How would I accomplish the following?
I have a dataset that roughly looks like the following:
Question, Company, Value
1, CompanyA, 10000
1, CompanyB, 20000
1, CompanyC, 30000
2, CompanyA, 1
2, CompanyB, 5
2, CompanyC, 11
3, CompanyA, 0
3, CompanyB, 3
3, CompanyC, 12
In the real dataset there are more like 200 companies, not just A, B, C. How would I quickly add a new column that would evaluate each response and assign a score of 1-5 depending on their ranking (in relative bands). Meaning, for Question 1 the highest 20% scores would receive a 5, the next 20% would receive a 4, and so on. The challenge is that for some questions the higher the better, for others the lower the better.
Thoughts!?
Thank you in advance!
