Calculated Column Formula Not Working

New Contributor

I'm trying to get a column within SharePoint that references another column.

 

Basically:

 

If the Version is <1, column returns Stage 1

If the Version is =1, column returns Stage 2

If the Version is <2, column returns Stage 3

If the Version is =2, column returns Stage 4

 

I have tried the following formula:

=IF([Version]<1,"Stage 1",IF([Version]=1,"Stage 2", IF([Version]<2,"Stage 3", IF([Version]=2,"Stage 4 - Closed"))))

 

In Excel that works sufficiently but when copying this to SharePoint it doesn't work.

 

sharepointmonions_0-1667926535693.pngsharepointmonions_1-1667926556847.png

 

 

 

4 Replies

@sharepointmonions I'll have a look at this but your logic is flawed: under your formula if version is less than 2 then it could return either Stage 1,2 or 3!

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

@sharepointmonions What do you mean by it doesn't work, is it throwing any error or it is not returning expected results?

 

If Version column is of type Number, use this formula for your calculated column: 

 

=IF([Version]<1,"Stage 1",IF([Version]=1,"Stage 2",IF([Version]<2,"Stage 3",IF([Version]=2,"Stage 4 - Closed", ""))))

 

Note:

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

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Hi @ganeshsanap it was saying there was a technical error, but your formula is accepted. However, it doesn't return Stage 2 for the document that is Version 1:

Even when I just did =IF([Version]<1,"Stage 1","Stage 2") it would still return Stage 1 for the document that is at Version 2.

@sharepointmonions What is the column type of Version column?

 

If it is a single line of text, try using: 

 

=IF(VALUE([Version])<1,"Stage 1",IF(VALUE([Version])=1,"Stage 2",IF(VALUE([Version])<2,"Stage 3",IF(VALUE([Version])=2,"Stage 4 - Closed", ""))))

 


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.