Sharepoint 2013 Calculated Column formula

Hi,
This example to simplify:

Column1 has four rows with different values: A,B,C,D
I need Column2 to have a formula presenting "YES" if Column1 is A or B.
(and "NO" if C or D)

Anyone?

Brgds
Håvard
(on-premise sharepoint 2013)
Håvard LillevikAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rainer JeschorCommented:
Hi Håvard,
sorry but what do you mean with
Column1 has four rows with different values: A,B,C,D
?

Is column 1 an choice field and you have four different values to select?

Normally the calculated column should look like this:
=IF(OR([Column1]="A",[Column1]="B"),"YES",IF(OR([Column1]="C",[Column1]="D"),"NO","N/A"))

Open in new window


HTH
Rainer
Håvard LillevikAuthor Commented:
Hi Rainer,
and thanks for your reply.

Column1 is a multiple choise field.
(production list is quite large and column1 has aprox 30 different values to choose from)

Brgds
Håvard
Rainer JeschorCommented:
Hi Håvard,
OK that makes more sense.
Unfortunately this type of field/column is not supported in calculated columns formulas.
What do you want to achieve with this information? Just displaying on a list view page? If yes there is the option to use client side scripting to adjust the displayed table (and e.g. add dynamically an additional column).
Do you use SharePoint Server 2013 or SharePoint Foundation 2013?

Thanks.
Rainer
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Håvard LillevikAuthor Commented:
Hi Rainer,
That didnt sound like good news...

Reason for the desired functionality is:
The List is quite large and is used for systemdocumentation of numerous clients. We want to make views per client so that we easily can display that clients vital infromation in a webpart.
However - this client view will only be a "front page" view displaying only vital information.
Thats why I thougt we could have som sort of an IF-formula saying that if column1 is A or B column2 is "YES" and "NO" if not. Then I could make the clients "front page" view based on a filter saying that if Column2 is "YES" then it should be displayed in the front page view.

Did that make sense?


We are running on SharePoint Server 2013 Enterprise.

Thanks
Håvard
Håvard LillevikAuthor Commented:
Hi again,
Found this one that seems to be the solution but i cant get it to work:


Column1 has the value of BD122

Formula:
=EXACT([Column1], "BD122")  

Expected result:
Compares contents of Column1 and the string "BD122" (Yes)

ref: http://msdn.microsoft.com/en-us/library/bb862071.aspx
Håvard LillevikAuthor Commented:
Found the solution!!
and it was obvious (or not) as usual....

due to language settings "," is not aloud because our version treats it as a desimal. Using ";" everything works perfectly.

Simplyfied working formula looks like this:
=IF([Type Description]="Wireless";"YES";IF([Type Description]="UPS";"YES";IF([Type Description]="Generell informasjon";"YES")))

Thanks:-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rainer JeschorCommented:
Hi Håvard,
thats correct. And sorry - I had a wrong understanding of
multiple choise
(yes - I should read questions and comments more carefully - sorry) - I interpreted as Multi-Select column - and they are not supported :-)

KR
Rainer
Håvard LillevikAuthor Commented:
no worries and thanks!
Your reply lead me in the right direction anyways:-)
Rainer JeschorCommented:
Hi,
no problem. I have also two additional topics to mention:
- Nesting IF limitations
There is a hard limitation of 7 nested IFs and a calculated column formula length limitation of about 1023 characters
Workaround:
Build the first 7 nested IFs and then concatenate the next IFs with a simple "&" (without the quotes).

- Question Closing
For further questions I would recommend that it is always possible to close a question based on a solution you found (with 0 points) but assigning parts or all points to other experts comments when they got you in the right direction as "Assisted Solution". That will make us experts at least happy that our comments leaded to the right direction

Thanks and KR
Rainer
Håvard LillevikAuthor Commented:
Tip! check your language settings when experiencing syntax error in formulas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.