Populating certain cells based off a point system value

[Hollywood9819]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]rating
[/TD]
[TD]5-advanced
[/TD]
[TD]4- proficient
[/TD]
[TD]3- competent
[/TD]
[TD]2-developing
[/TD]
[TD]1- learning
[/TD]
[TD]0-nonperformer
[/TD]
[/TR]
[TR]
[TD]range
[/TD]
[TD]96-100
[/TD]
[TD]90-95
[/TD]
[TD]81-89
[/TD]
[TD]76-80
[/TD]
[TD]70-75
[/TD]
[TD]64-69
[/TD]
[/TR]
[TR]
[TD]earned
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Issue: I am trying to auto-populate a score in the "earned" row in the appropriate column based off a score in a separate cell from another sheet. I'm not even sure how to set up a formula for this other than assuming I would use an "IF" formula. Assuming the chart above is using rows 1-3 and columns A-G, can anyone provide assistance to this problem?

 

[onlyadrafter]

Hello,

In the advanced cell use

=IF(AND(Sheet4!$A$1>=96,Sheet4!$A$1<=100),5,"")

moving across

=IF(AND(Sheet4!$A$1>=90,Sheet4!$A$1<=95),4,"")
=IF(AND(Sheet4!$A$1>=81,Sheet4!$A$1<=89),3,"")
=IF(AND(Sheet4!$A$1>=76,Sheet4!$A$1<=80),2,"")
=IF(AND(Sheet4!$A$1>=70,Sheet4!$A$1<=75),1,"")
=IF(AND(Sheet4!$A$1>=64,Sheet4!$A$1<=69),0,"")

Not sure what you want to do if score is below 64, I assume this won't happen.

I would advise putting the score ranges and the rating is separate cells so you can reference them.

 

[Hollywood9819]

There is nothing I can do with the format as it is a set template I can't adjust for work. Thanks for the help though!!!

 

[onlyadrafter]

Hi,

In your scenario above, what do want to appear in the 3rd row under 76-80?

 

[Peter_SSs]

.. based off a score in a separate cell from another sheet.

What sheet?
What cell(s) in that other sheet?