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