SOLVED

List with calculated column based on data in row?

Copper Contributor

Hi, 

So I have a SharePoint list which has:

A product column with lets say 5 different products, so 5 different rows.

Then two columns which are for August and July (I have all the months but trying to keep it simple) and these have manually input numbers in.

And then I have a calculated column

I want the calculated column to do the calculation ([AUG-JUL])/30. However I only want it to do this calculation for products 1-3, For products 4-5 I want it to do the calculation ([AUG-JUL])/45. Is this even possible to do in a sharepoint list? I've been trying to play around with it but keep getting syntax error.

Any input is much appreciated.

Thanks

3 Replies
best response confirmed by Tom898 (Copper Contributor)
Solution

@Tom898 Try using calculated column formula like: 

 

=IF(OR([Product]="Product 1",[Product]="Product 2",[Product]="Product 3"),([AUG]-[JUL])/30,([AUG]-[JUL])/45)

 

OR 

 

=IF(OR([Product]="Product 1",[Product]="Product 2",[Product]="Product 3"),([AUG]-[JUL])/30,IF(OR([Product]="Product 4",[Product]="Product 5"),([AUG]-[JUL])/45,""))

 

Note:

  1. Sometimes comma( , ) does not work in formula (it is based on language or regional settings of your SharePoint site). So in that case use semicolon( ; ) instead of comma( , ).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

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.

@ganeshsanap Hi, I have just tried your method however am still getting the error screen stating that there is a syntax error or it is not supported. The image shows what I have input

Tom898_0-1719476951792.png

 

Worked it out now, thank you
1 best response

Accepted Solutions
best response confirmed by Tom898 (Copper Contributor)
Solution

@Tom898 Try using calculated column formula like: 

 

=IF(OR([Product]="Product 1",[Product]="Product 2",[Product]="Product 3"),([AUG]-[JUL])/30,([AUG]-[JUL])/45)

 

OR 

 

=IF(OR([Product]="Product 1",[Product]="Product 2",[Product]="Product 3"),([AUG]-[JUL])/30,IF(OR([Product]="Product 4",[Product]="Product 5"),([AUG]-[JUL])/45,""))

 

Note:

  1. Sometimes comma( , ) does not work in formula (it is based on language or regional settings of your SharePoint site). So in that case use semicolon( ; ) instead of comma( , ).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

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.

View solution in original post