Getting a specified drop down selection based on the range from another cell

JessAr

I have a sheet that is for prioritization purposes, there are multiple columns that aggregate value into a "Total Score" column, I want my "Priority" column to auto populate a value based on the value in total score ie if value 0-20 it should be P4 if it is 21-40 P3 if 41-60 P2 if 61-80 P1 if 81-100 P0. I used the following IF formula but get unparseable error

=IF([Total Score]@row  = 100, "P0", IF([Total Score]@row  < 80, "P1", IF([Total Score]@row  < 60, "P2", IF([Total Score]@row  < 40, "P3", IF([Total Score]@row  < 20, "P4")))))) 

DKazatsky2

Hi @JessAr,

You may have 1 too many ")" at the end. Try removing one and see what happens.

Hope this helps,

Dave

Kerry St. Thomas

Setting aside the error (which I think is probably associated with too many close-parentheses), everything under 80 would return P1, 100 would return P0, and nothing would happen for between 100 and 80. I find it easier to go the other direction - bottom up, instead of top down on your score scale. I also added a thing at the beginning to NOT show a Priority rate if there's no score.

=IF(ISBLANK([Total Score]@row), "", IF([Total Score]@row <= 20, "P4", IF([Total Score]@row <= 40, "P3", IF([Total Score]@row <= 60, "P2", IF([Total Score]@row <= 80, "P1", IF([Total Score]@row < 100, "P1", "P0"))))))

Good luck!

DKazatsky2

Hi @JessAr,

You may have 1 too many ")" at the end. Try removing one and see what happens.

Hope this helps,

Dave

Kerry St. Thomas

Setting aside the error (which I think is probably associated with too many close-parentheses), everything under 80 would return P1, 100 would return P0, and nothing would happen for between 100 and 80. I find it easier to go the other direction - bottom up, instead of top down on your score scale. I also added a thing at the beginning to NOT show a Priority rate if there's no score.

=IF(ISBLANK([Total Score]@row), "", IF([Total Score]@row <= 20, "P4", IF([Total Score]@row <= 40, "P3", IF([Total Score]@row <= 60, "P2", IF([Total Score]@row <= 80, "P1", IF([Total Score]@row < 100, "P1", "P0"))))))

Good luck!