Forum Discussion

Lpertence's avatar
Lpertence
Copper Contributor
Aug 29, 2022

Formula help needed

Hi all.

 

I need help with a simple formula, I've been trying for days, but I'm unable to find a solution...

 

In a list, I have:

  • A column called [Power] with choices from 1 to 4.
  • A column called [Impact] with choices from 1 to 4.
  • A column called [Total_Score] with formula= [Power]*[Impact].

My quest:

A column called [Management_Style], a calculated column, where I would like to make a matrix (powerXImpact)

 

Matrix:

 

If power =3 or 4, and impact = 3 or 4; then "Manage Closely"

If power= 3 or 4, and impact =1 or 2; then "Keep Satisfied"

if power = 1 or 2, and Impact = 3 or 4; then "Keep Informed"

If power= 1 or 2, and impact= 1 or 2; then "Monitor (Minimum Effort)"

 

I tried many types if multiple IFs, ANDs, ORs, but I could not find the right formula.

 

Any help is really appreciated.

Thank you all!

  • Lpertence 

     

    You should try below formula:

     

     

    =IF(AND(OR(Power="3",Power="4"),OR(Impact="3",Impact="4")),"Manage Closely","")&IF(AND(OR(Power="3",Power="4"),OR(Impact="1",Impact="2")),"Keep Satisfied","")&IF(AND(OR(Power="1",Power="2"),OR(Impact="3",Impact="4")),"Keep Informed","")&IF(AND(OR(Power="1",Power="2"),OR(Impact="1",Impact="2")),"Monitor (Minimum Effort)","")

     

     

    See output as below:

     

     

     

    Official Documentation:

    https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14) 

     

    Important:

    All example formulas in this topic use commas "," as the parameter delimiter character. In some countries, the comma is reserved for use as the decimal mark. In such countries, users creating a calculated field must use semi-colons ";" as the delimiter character. Regardless of which character is used when the field is created, the formula works on lists in SharePoint websites anywhere in the world. SharePoint automatically changes the delimiter character to the one that is appropriate for the language/culture of the current page. For example, suppose the following formula is created on a website whose culture setting is fr-fr (France): =IF(Number1>Number2;5;10). If the website's culture is then changed to en-us (United States), the formula changes automatically to: =IF(Number1>Number2,5,10).

     

     


    Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community

     

  • Lpertence 

     

    You should try below formula:

     

     

    =IF(AND(OR(Power="3",Power="4"),OR(Impact="3",Impact="4")),"Manage Closely","")&IF(AND(OR(Power="3",Power="4"),OR(Impact="1",Impact="2")),"Keep Satisfied","")&IF(AND(OR(Power="1",Power="2"),OR(Impact="3",Impact="4")),"Keep Informed","")&IF(AND(OR(Power="1",Power="2"),OR(Impact="1",Impact="2")),"Monitor (Minimum Effort)","")

     

     

    See output as below:

     

     

     

    Official Documentation:

    https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14) 

     

    Important:

    All example formulas in this topic use commas "," as the parameter delimiter character. In some countries, the comma is reserved for use as the decimal mark. In such countries, users creating a calculated field must use semi-colons ";" as the delimiter character. Regardless of which character is used when the field is created, the formula works on lists in SharePoint websites anywhere in the world. SharePoint automatically changes the delimiter character to the one that is appropriate for the language/culture of the current page. For example, suppose the following formula is created on a website whose culture setting is fr-fr (France): =IF(Number1>Number2;5;10). If the website's culture is then changed to en-us (United States), the formula changes automatically to: =IF(Number1>Number2,5,10).

     

     


    Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community

     

Resources