# Calculated column - calculate percentage done based on status

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.

Try below function hope it will help with multiple if condition

``````=IF([Status]="planned","5",IF([Status]="writing", "30", IF([Status]="technical review", "50")))
``````
``````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.

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

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)

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
...
``````
• 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) 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" ) ) ) Jan 4, 2016 at 15:21