Forum Discussion
Lpertence
Aug 29, 2022Copper Contributor
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!
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:
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
- kalpeshvaghelaSteel Contributor
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:
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