Excel 2014
sonofgods
Houston TX Member
Hello i need help. I have to make a new point system for Class.
A are the name of the people in class. B is the points they earned. and c is the rank they should have or earn. I want it to take B and put it in a range system like 1-100=PVT 101-200=PFC 201-300=CPL 301-400=SGT and so on. I feel like it super easy and im over looking it.
0
Comments
there is 137 people in the class. and the Ranking goes on for a long time. and i want it to be easy for new people to use. where they can go from 100 to 101 and the name in C will change automatically
http://stackoverflow.com/questions/14673392/set-text-of-cell-if-adjacent-cell-is-between-specified-values
The first option is how I would do it because that was what I knew and it is easy to understand. The second option is cleaner and more straightforward once you understand vlookup.
You need nested if/then statements.
Is this an assignment for a class? If not, I have an Excel gradebook I made for myself that I can share with you as an example.
You can also "extend" the function if you use the vlookup way (the if,else way will be more confusing) by using an if at the beginning.
=IF(B1="","",VLOOKUP(B1,Thresholds!$A:$B,2))
so that read: IF the cell I'm testing is empty, make the cell I'm trying to put a value in empty, otherwise use the value from the vlookup.
its for Jrotc. We have a point system but it broken and doesnt work. im try what you guys told me and report if it worked or not
Thank you @Tushon and @CB
=IF( B1<=99, "PVT", IF( B1<=199, "PFC", IF( B1<=299, "CPL",) ) ) is the one i like. It was easy for me to understand. now is there a way i can Copy and paste is all the way done the sheet so that the B1 will change to b2, b3, b4 and so on? i cant believe i had a class over this and i still dont know how to do this. ;-;
=IF( B1<=99, "PVT", IF( B1<=199, "PFC", IF( B1<=299, "CPL", IF(B1<=399, "SGT", IF(B1<=499, "SSG", IF(B1<=599, "SFC", "MSG")))))) This is prefect to what i need.
NVM forgot drag the plus
Just to clarify, your original requirements indicate that the B1<= should be 100, 200, 300, etc not 99, 199, ...
The vlookup way is easier to understand from a visibility perspective and less complicated to maintain if there happen to be changes in the future, but like I said, I thought of nested if's first because I've used it before.
Will I'll open two documents and test them both. I'll have to pass this on to different people. So it should be easier for them not me.
And the reason I have it at 99. Is so as soon as it hit 100 it goes up
I see. That's not what you listed at the top, hence my confusion. The vlookup way is more managable and readable, which is a good path to go down if you're learning any sort of programming (from excel "scripting" to full languages).
Thank you Tushon.