Excel 2014

sonofgodssonofgods 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.

Comments

  • sonofgodssonofgods Houston TX Member

    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

  • TushonTushon I'm scared, Coach Alexandria, VA Icrontian
    edited March 2015

    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.

    sonofgods
  • CBCB Ƹ̵̡Ӝ̵̨̄Ʒ Der Millionendorf- Icrontian

    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.

    sonofgods
  • TushonTushon I'm scared, Coach Alexandria, VA Icrontian

    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.

    _k
  • sonofgodssonofgods Houston TX Member

    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

  • sonofgodssonofgods Houston TX Member

    =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. ;-;

  • sonofgodssonofgods Houston TX Member

    =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.

  • sonofgodssonofgods Houston TX Member

    NVM forgot drag the plus

  • TushonTushon I'm scared, Coach Alexandria, VA Icrontian

    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.

    Kwitko
  • sonofgodssonofgods Houston TX Member

    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.

  • sonofgodssonofgods Houston TX Member

    And the reason I have it at 99. Is so as soon as it hit 100 it goes up

  • TushonTushon I'm scared, Coach Alexandria, VA Icrontian

    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).

  • sonofgodssonofgods Houston TX Member

    Thank you Tushon.

Sign In or Register to comment.