Many Nested IFs in Calculated Sharepoint Column and I now have a headache!

Question

Hello!

I've been playing around at this for about 3 hours and I don't want to anymore!

Here's what I want to do:

Column: PERFORMANCE (Exceeds, Meets, Needs Improvement)

Column: POTENTIAL (Breadth, Balanced, Depth)

I'm trying to write an IF statement for a new Sharepoint column, Talent Rating.

What I want to do is have it be as follows:

Exceeds + Breadth = 9

Exceeds + Balanced = 8

Exceeds + Depth = 7

Meets + Breadth = 6

Meets + Balanced = 5

Meets + Depth = 4

Needs + Breadth = 3

Needs + Balanced = 2

Needs + Depth = 1

I have tried so many different combos of IF and AND that I am no longer convinced that IF and AND are correctly spelled.

Any ideas??

Answer 1

Dear KSP,

Here is a demo for your reference. You can use the formula below to see the result.

=IF(AND(Performance="Exceeds",Potential="Breadth"),9,IF(AND(Performance="Exceeds",Potential="Balanced"),8,IF(AND(Performance="Exceeds",Potential="Depth"),7,IF(AND(Performance="Meets",Potential="Breadth"),6,IF(AND(Performance="Meets",Potential="Balanced"),5,IF(AND(Performance="Meets",Potential="Depth"),4,IF(AND(Performance="Needs Improvement",Potential="Breadth"),3,IF(AND(Performance="Needs Improvement",Potential="Balanced"),2,IF(AND(Performance="Needs Improvement",Potential="Depth"),1)))))))))

Welcome to share any updates when you have time if you need further help on this issue.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator

Answer 2

OH MY GOODNESS, IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thank you so much!!!

Answer 3

Dear KSP,

Thanks for your updates.

I'm glad to hear that the issue has been solved.

Welcome to post a new thread on our forum if you encounter any issues in your environment.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator