CODEWITHSUNDEEP
excel
JOE CARROLL
JOE CARROLL

Reputation: 1

Auto-populate Cell In Excel Based On Contents Of Another Cell

I am having a hard time grasping coding. My Excel sheet has a cell containing a score. I would like whatever the score is to populate a rating in a different cell:

Upvotes: 0

Views: 2389

Answers (4)

Scott Craner
Scott Craner

Reputation: 152450

INDEX/MATCH:

=INDEX({"Poor","Below Averrage","Average","Above Average","Excellent"},MATCH(A1,{-1E+99,-3,-1,2,4}))

enter image description here

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

With a score in A1, in another cell enter:

=CHOOSE(A1+6,"Poor","Poor","Below Average","Below Averrage","Average","Average","Average","Above Average","Above Average","Excellent","Excellent")

enter image description here

Upvotes: 0

dwirony
dwirony

Reputation: 5450

You have to use nested IFs, and a combination of OR or AND (just user preference really). Observe this nested IF formula:

=IF(OR(A1=-4,A1=-5),"Poor",IF(OR(A1=-2,A1=-3),"Below Average",IF(AND(A1>=-1,A1<=1),"Average",IF(OR(A1=2,A1=3),"Above Average",IF(OR(A1=4,A1=5),"Excellent")))))

Putting this in B1 and having it read values off A1 produces the following (and dragging it down will automatically change it to read off the adjacent cell)

Image1

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You could write a simple UDF

Option Explicit

Public Function Score(ByVal rng As Range) As String
    If rng.Count > 1 Then
        Score = "Select single cell"
        Exit Function
    End If

    Select Case rng
    Case 4, 5
        Score = "Excellent"
    Case 2, 3
        Score = "Above Average"
    Case 1, 0, -1
        Score = "Average"
    Case -2, -3
        Score = "Below Average"
    Case -4, -5
        Score = "Poor"
    Case Else
        Score = "Unknown"
    End Select
End Function

Example use of UDF in sheet:

UDF call

Developer Tab added to ribbon > open with Alt + F11 and add a standard module and place code in that:

Code in a standard module:

Code in module

If you set your data up as a table and enter the formula in the top right hand cell it will auto-populate the formula down all the rows:

Table filldown

Upvotes: 1

Related Questions