Need some help with Microsoft Excel (assigning grades to responses)

so I've got 113 surveys that have response options as such:
Poor
Fair
Adequate
Good
Excellent

there were four different questions where you could select a single option from above in each question.

What I want to do is, in essence, grade my department's effectiveness by assigning numbers like so to each response:
Poor: 0%
Fair: 25%
Adequate: 50%
Good: 75%
Excellent: 100%

From giving those descriptors a number, I want to be able to say, "hey the department averages a 90% (an A) if we give responses these values"

what Excel function(s) do I need? I want to have it graded (by traditional letter grades and percentages) to see how we're excelling (no pun intended) potentially in one aspect over another, etc.

I think I need a mix of IF and VLOOKUP, but I'm not entirely sure.

Thanks for any help guys!

edit: I do have the responses (e.g, how many people said "Excellent" for question 1) summed up in their own areas on a single sheet (the raw responses per survey are on a separate sheet), if it makes a difference

=IF(B3="", 0, IF(B3="a", 100, IF(B3="a-", 92, IF(B3="b+", 89, IF(B3="b", 85, IF(B3="b-", 82, IF(B3="c+", 79, IF(B3="c", 75, IF(B3="c-", 72, IF(B3="d+", 69, IF(B3="d", 65, IF(B3="d-", 62, 0))))))))))))

Above is method I use to convert letter grades to number grades. It would work just as well for you. Simply change letters like "A" to whatever word you wish to assign a number to "excellent". You could do it with vlookup as well but I use my phone to calculate grades and arrays don't work in office for android.

If all you care about is the overall average you could just do the cell with the number of excellents x 100 + the cell with the number of goods x 80 etc then divide by the total number of responses.

I would use VLOOKUP and use that table with your assigned numbers:
Poor: 0%
Fair: 25%
Adequate: 50%
Good: 75%
Excellent: 100%
Use the AVERAGE function to get the average. Then use IF to get your grade A, B, ect..

recDNA said:

=IF(B3="", 0, IF(B3="a", 100, IF(B3="a-", 92, IF(B3="b+", 89, IF(B3="b", 85, IF(B3="b-", 82, IF(B3="c+", 79, IF(B3="c", 75, IF(B3="c-", 72, IF(B3="d+", 69, IF(B3="d", 65, IF(B3="d-", 62, 0))))))))))))

Above is method I use to convert letter grades to number grades. It would work just as well for you. Simply change letters like "A" to whatever word you wish to assign a number to "excellent". You could do it with vlookup as well but I use my phone to calculate grades and arrays don't work in office for android.

If all you care about is the overall average you could just do the cell with the number of excellents x 100 + the cell with the number of goods x 80 etc then divide by the total number of responses.

Click to expand...

Redhat703 said:

I would use VLOOKUP and use that table with your assigned numbers:
Poor: 0%
Fair: 25%
Adequate: 50%
Good: 75%
Excellent: 100%
Use the AVERAGE function to get the average. Then use IF to get your grade A, B, ect..

Click to expand...

thank you both so much! Truth be told, a few hours later I decided to just do it on a scale of 1-5 for the responses and average out the numbers. My logic was such that it still may need to be forward-looking in the sense that others will probably use this as a template once I'm done with my internship, and I know Excel doesn't come easy to many.

Thanks guys, I really appreciate the aid!