Calculated column - calculate percentage done based on status

Question

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.

Answer 1

Try below function hope it will help with multiple if condition

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

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.

Answer 3

 =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
            ...