SP Calculated Columns with nested IF statements

Question

I'm looking for some advice on nested IF statements within SP as what I am trying keeps giving me errors. I'm trying to get my statement correct and if I can't should I be using a flow instead of the calculated column.

I have a choice column [System Architecture] consisting of 6 choices, each choice has a review cycle added to it which converted into days will determine my next review due date. I can't seem to get this right.

So the choices are and what values I want them to return:

• A = 365 days
• B = 1460
• C = 730 days
• D = 1095 days
• E and F = 0 days

=IF(([System Architecture]="A","365"),IF([System Architecture]="B","1460"),IF([System Architecture]="C","730"),IF([System Architecture]="D","1095"),IF([System Architecture]="E","0"),IF([System Architecture]="F","0"))

I have managed to get one IF statement to work but as soon as I add my additional statements in, I only get syntax errors. Single working IF Statement:

=IF([System Architecture]="A","365")

Any advice would be greatly appreciated.

Answer 1

Use this formula for your calculated column:

=IF([System Architecture]="A","365",IF([System Architecture]="B","1460",IF([System Architecture]="C","730",IF([System Architecture]="D","1095",IF(OR([System Architecture]="E",[System Architecture]="F"),"0","")))))

Note:

1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

Official Documentations:

1. Calculated Field Formulas.
2. IF function.