SOLVED

# SP Calculated Column Formula Help

Frequent Contributor

# 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?

8 Replies

# Re: SP Calculated Column Formula Help

@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=offic...

# Re: SP Calculated Column Formula Help

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

# Re: SP Calculated Column Formula Help

Thanks @ganeshsanap

Is it possible to add more names to the FIND? e.g. "Amazon, Ebay, Google", etc.?

# Re: SP Calculated Column Formula Help

@jonboylib You need to add nested IF conditions for that.

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.

# Re: SP Calculated Column Formula Help

@ganeshsanap

Ideally I would like to search for multiple suppliers. So rather than just Amazon, could I add more to list this, e.g. something like

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

# Re: SP Calculated Column Formula Help

@jonboylib How many suppliers you want to search in formula?

You can try below formula:

``=IF(OR(ISNUMBER(FIND("Amazon", [Supplier])), ISNUMBER(FIND("Google", [Supplier])), ISNUMBER(FIND("Ebay", [Supplier]))), [Net], [Net] * 1.2)``

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.

# Re: SP Calculated Column Formula Help

@ganeshsanap

I don't have a set number it will vary. I was just wondering what the logic would be.

Is there a limt to the number of embedded FIND I can use for example?

Thanks for your example it's very useful.
best response confirmed by jonboylib (Frequent Contributor)
Solution

# Re: SP Calculated Column Formula Help

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