• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help me with these 3 vlook up problems. Excel Sheet is attached.

Status
Not open for further replies.

fahadferoz

Member
Question 1:
Springfield University has over 5,000 students. Each student is assigned a four-letter Status, which is either UNCL (unclassified), UGRD (undergraduate), or GRAD (graduate). The first digit of the student number reflects the four-letter Status:
  • If the first digit of the student number is 1 or 2, then the Status is UNCL.
  • If the first digit of the student number is 3, 4, or 5, then the Status is UGRD.
  • If the first digit of the student number is 6, 7, 8, or 9, then the Status is GRAD.
No student has a student number beginning with the number 0. Furthermore, each student number consists of exactly six digits. Assume for the purposes of the following three questions, and for simplicity, that a student cannot change Status.

Professor Grey is an instructor of COMM 499, an undergraduate course at the Faculty of Commerce at Springfield U that is open to all students regardless of their status. This means that even students with GRAD and UNCL status (in addition to those with UGRD status) are allowed to take the course. The grading system used for UNCL and UGRD students, however, is different from the grading system used for GRAD students.

First, Professor Grey needs to complete column B of his class list (in an Excel spreadsheet) to assign each student to the correct four-letter Status. He wants to use a VLOOKUP function. Professor Grey intends to type up the formula in cell B2, and then drag the formula down to cell B15. The lookup table will be in F3:G5.

Complete the lookup table F3:G5.

Question 2:

Assume that Professor Grey has completed the lookup table F3:G5 correctly.

Recall that Professor Grey needs to complete column B of his class list (in an Excel spreadsheet) to assign each student to the correct four-letter Status. He wants to use a VLOOKUP function. Professor Grey intends to type up the formula in cell B2, and then drag the formula down to cell B15.

Complete the formula in cell B2. Remember that the formula, when dragged down to cell B15, must work automatically without the need to manually edit it.

Question 3:
Now, Professor Grey wants to assign the letter grade for each student in his class. If the student is an undergraduate or an unclassified student, s/he will receive a letter grade based on the table I3:J13. If the student is a graduate student, s/he will receive a letter grade based on the table L3:M12.

The threshold is the minimum percent grade required to attain the corresponding letter grade.

Complete the following formula in cell D2. The formula must work properly when dragged down to cell D15, and it must not be changed manually.
 

Attachments

  • vlook up problem.xlsx
    37.5 KB · Views: 2
Last edited:
Not until you tell us what you have tried. What have you tried that isn't working?

Help does not mean doing it for you. :)
 
Maybe someone will do it for you, and then you will submit their solution as your work, get the credit for it and learn NOTHING. That's your choice - more fool you (and anyone who hands you the solution on a plate). :)
 
No, it's not offensive,. If you want to learn, then what's the problem? Surely you have tried something before turning to us for help? So, what is it that you have tried? We can help you to identify errors in your thinking, and that way you will get something out of the process.
 
instead of contructive talking this woman started policing > then started lecturing. of course i tried and could not do it. do you want me to say that? please stop posting on this thread until you change your writing tone. if you are the moderator just delete my post. Not interested in taking more lectures from you.
 
fahadferoz,

I think it's you who needs to change tone here. Cross-posting without notifying, being oh so defensive, not answering the question what you tried so we could help - so who's not constructive? - instead you are being not so polite while Ali is just being honest we don't like doing homework for others. Yes when you show us what you've tried as formula we can offer assistance.
I wonder did you ever read the forum rules?
 
Status
Not open for further replies.
Back
Top