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.

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"))) 
| improve this answer | |
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.

| improve this answer | |
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
            ...
| improve this answer | |
  • 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 '16 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 "&". – Bhaskar Dhone Jan 4 '16 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 '16 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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