Calculated column - using multiple AND and IF functions

Question

I have been trying to figure this out and I cannot solve it.

I am trying to create something which say IF column a = "High" AND Column b ="High" THEN "Red" should be inputted. So far I have...

=IF(
    ([Column a]="High" AND([Column b]="High") ) 
    ,"Red"
    , IF(
         ([column a]="High" AND([Column B]="Low") )
         ,Gold"
         , IF(
              ([Column a]="Low" AND([Column b]="High") )
              ,"Green"
              , IF(
                   ([Column a]="Low" ( AND[Column b]="Low") )
                   ,"Black"
                   , ("n/a")
                  )
              )
        )
  )

Thanks in advance

Danny '365CSI' Engelman · Accepted Answer · 2015-09-11 12:01:00Z

6

The formula will be something like this. I have not tested it, but it should work:

=IF(
    AND([Column A]="High",[Column b]="High")
    ,"Red"
    ,IF(
        AND([Column A]="High",[Column b]="Low")
        ,"Gold"
        ,IF(
            AND([Column A]="Low",[Column b]="High")
            ,"Green"
            ,IF(
                AND([Column A]="Low",[Column b]="Low")
                ,"Black"
                ,"NA"
               )
           )
      )
  )

Reference: Calculated Column formulas

edited Sep 11, 2015 at 12:01

Danny '365CSI' Engelman

21.2k7 gold badges37 silver badges80 bronze badges

answered Sep 9, 2015 at 6:57

P S

4,8274 gold badges29 silver badges51 bronze badges

IT WORKED!!!! Thank you so much!!! I killed my brain last night trying to work this out

Stephanie
– Stephanie

2015-09-09 22:30:11 +00:00
Commented Sep 9, 2015 at 22:30

Add a comment |

Danny '365CSI' Engelman · Accepted Answer · 2020-06-26 08:28:02Z

Correct, contrary to programming languages, SharePoint has Logical Functions, not Operators.
Note: A SharePoint Function can (only) take up to 30 parameters..

More info on available Functions at: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

If status is always high/low you can condense your Formula to:

=IF(
    [Column A]="High"
    ,IF([Column b]="High"
        ,"Red"
        ,"Gold"
        )
    ,IF([Column b]="High"
        ,"Green"
        ,"Black"
        )
  )

You can copy/paste Formula from here into SharePoint; on save SharePoint will remove all newlines and spaces

Update #1

When you do want the NA option, rewrite it to a way better readable Formula:

=IF([Column A]&[Column b]="HighHigh"
    ,"Red"
    ,IF([Column A]&[Column b]="HighLow"
        ,"Gold"
        ,IF([Column A]&[Column b]="LowHigh"
            ,"Green"
            ,IF([Column A]&[Column b]="LowLow"
                ,"Black"
                ,"NA"
            )
        )
    )

Stack Exchange Network

Calculated column - using multiple AND and IF functions

2 Answers 2

Update #1

Your Answer

Hot Network Questions

Calculated column - using multiple AND and IF functions

2 Answers 2

Update #1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions