Forum Discussion

jonboylib's avatar
jonboylib
Iron Contributor
Jul 20, 2022
Solved

SP Calculated Column Formula Help

I want to check if a column Supplier does not contain the word Amazon, if true then multiply the value of Net by 1.2. This is what I have but it's not working;

 

=IF(Supplier=NOT("Amazon"),Net*1.2,Net)

 

It's displaying #Name?

 

Can someone help please?

  • jonboylib The logic is, you have to add ISNUMBER(FIND(...)) part for each supplier inside OR() function.

     

    You can have maximum 30 such conditions inside single OR function.

     

    Can you explain further what is your requirement exactly? There might be another way to achieve it.


    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.

8 Replies

  • jonboylib Try using below calculated column formula, it should work for you: 

     

    =IF(ISNUMBER(FIND("Amazon", [Supplier])), [Net], [Net] * 1.2)

     

    DocumentationExamples of common formulas in lists  


    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.

    • jonboylib's avatar
      jonboylib
      Iron Contributor
      Thanks ganeshsanap

      Is it possible to add more names to the FIND? e.g. "Amazon, Ebay, Google", etc.?
      • ganeshsanap's avatar
        ganeshsanap
        MVP

        jonboylib You need to add nested IF conditions for that.

         

        Can you explain further about your requirements for adding more names? What should be the calculation (example: Net*1.2) for additional names?


        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.

  • Bharath Arja's avatar
    Bharath Arja
    Iron Contributor

    jonboylib NOT function expects a logical expression. NOT(logical) - meaning it should evaluate to TRUE or FALSE. 

    Try =IF(ISNUMBER(FIND("Amazon",[Supplier])),Net*1.2,Net)

     

    https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14)#determine-whether-a-column-value-or-a-part-of-it-matches-specific-text

Resources