4

I'm trying to create a calculated column based on a status column. If the status is "planned", I'd like it to return 5%, if it is "writing", it should be 30%, if "technical review" it should be 50%, etc...

I have had luck with this formula:

=IF(  OR([Status]="Planned",[Status]="Writing")  , "5" , "30" ) 

But when I try to add more than the two statuses, I get the Error page. Does anyone have any suggestions about what I'm doing wrong. I have tried many different methods but none seem to work.

3 Answers 3

2

Try below function hope it will help with multiple if condition

=IF([Status]="planned","5",IF([Status]="writing", "30", IF([Status]="technical review", "50"))) 
0
2
IF([Status]="On Track","1",IF([Status]="Concern but Plan in place", "2", IF([Status]="Open need Plan", "3", "0"))) 

Column blank check you can perform as:

=IF(ISBLANK([My Column]),"EMPTY",[My Column])

For multiple checks:

Calculated column- evaluating multiple conditions

In this way you can build your formula.

1
 =IF(OR([Status]="Planned",[Status]="Writing"),"5","30") 

Does not really do what you might think, it does (in human language):

 IF Status IS "Planned" OR "Writing" THEN 5    ELSE 30

You can accept Bhaskar answer, so he receives the points..

Reformatted it reads better (and cou can copy/paste it like this, SharePoint will ignore linebreaks and redundant spaces, I usually prepare a Formula in Notepad)

 =IF([Status]="planned" , "5"
     ,IF([Status]="writing" , "30"
         ,IF([Status]="technical review" , "50"
            )
        )
     )

Couple of notes:

  • SharePoint only allows for 7 nested IF statements; you have to rework your formula if you need more nested checks.

  • The last IF has no ELSE clause, valid coding, but be aware SharePoint will make that a (default) FALSE value (for any status not mentioned in the IFs)

Update re: comments

You can not nest more than 7 levels deep

So you have to built Formulas like:

IF ( OR( 1 , 2 , 3 , 4 , 5 , 6 )
    , IF ( 1 
            , Do One
            , IF ( 2 
                , Do Two
                ...
    , IF ( 7
        , Do Seven
        , IF( 8
            , Do Eight
            ...
3
  • Oh wow!!! It works. Thank you so much for helping me with this! If I want to add more clauses, can I start a new formula? (I have more than 7 statuses)
    – Kristine
    Jan 4, 2016 at 9:41
  • Calculated Columns does not allow more than 7 Nested IFs. If you have more than 7 status in that case you can join two nested IF formulae with "&". Jan 4, 2016 at 9:54
  • This did not work. I got an error. I added this to the end: & =IF([Status]="Planned" , "5" ,IF([Status]="Pending" , "0" ,IF([Status]="On hold" , "0" ) ) )
    – Kristine
    Jan 4, 2016 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.