Calculated column formula works in Excel but not in SharePoint

Question

I have some help creating a calcualted column in the Excel forumns which works great in Excel but not in SP. I guess I shouldn't have assumed it would convert perfectly but it looks like SP doesn't like brackets "{" OR "}".

I have 3 columns ("CY T4", "CY T5", "CY Other"), all can have 1 of 3 different values (Yes, NR, NUL). I want mt calc column to display "Complete" OR "Not Complete" depending on the values of the 3 first columns.
 
If there is a "Yes" OR "NR" in all 3 columns it is "Complete", otherwise it is "Not Complete"
 
My Formula =IF(AND(OR(CY T4={"Yes","NR"}),OR(CY T5={"Yes","NR"}),OR(CY Other6={"Yes","NR"})),"Complete","Not Complete")
 
Can someone tell me if there a way to use this formula in SP or another approach to the same result.

---

Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.

Answer 1

Some more background.

I have already created a column called "CY Status" (picture) and use conditional formatting to display a graphic that will represent the status of all 3 "CY..." columns but can not group or filter by the CY Status column. I was hoping to some how group and/or filter this view so staff can see items that still require their attention. That is why I am trying the above formula on a calculated column so grouping was possible.

---

Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.

Answer 2

I have found a long solution to this issue.

I need a Calc column StatusCounter that will use the following formula.

• =IF([CY T4]="Yes",1,0)+IF([CY T5]="Yes",1,0)+IF([CY Other]="Yes",1,0)+IF([CY T4]="NR",1,0)+IF([CY T5]="NR",1,0)+IF([CY Other]="NR",1,0)

Then I can use another Cacl column (CY Status) to set the Complete/Not Complete by using a =IF([StatusCntr] = 3, "Complete","Not Complete").

---

Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.

Answer 3

I wonder if this would work?

=IF(AND(AND(OR(field1="Yes", field1="NR"), OR(field2="Yes", field2="NR")),OR(field3="Yes", field3="NR") ), "Complete", "Not Complete")

---

Robin

Answer 4

Robin,

You are correct this does work as well and is a bit cleaner as far as I can see but I'm not an expert. Since mine is setup already I'll probably leave it but will keep this format in mind as I believe there is a IF limit of 7 in a calculated column. At least that is what I have read.

---

Stunpals - Disclaimer: This posting is provided "AS IS" with no warranties.