1

I have a calculated column (see below) testing the content of six rating columns and it is working, however, I want to add a condition so that only those columns that are not null are tested. A list item may or may not have all six ratings but if all the ratings on a given item are low, then x = Low. I believe I can use ISBLANK but I can't get the formula to work. Can anyone help? Thanks in advance!

=IF(AND([Rate1]="Low",[Rate2]="Low",[Rate3]="Low",[Rate4]="Low",[Rate5]="Low",[Rate6]="Low"),"Low","Med")

|improve this question
1

How about this:

replace [Rate1]="Low"

with OR(ISBLANK([Rate1]),[Rate1]="Low")

Same for Rate2, Rate3, etc.

|improve this answer
  • =IF(AND(OR(ISBLANK([Rate1]),[Rate1]="Low"),OR(ISBLANK([Rate2]),[Rate2]="Low")="Low",OR(ISBLANK([Rate3]),[Rate3]="Low"),OR(ISBLANK([Rate4]),[Rate4]="Low"),OR(ISBLANK([Rate5]),[Rate5]="Low"),OR(ISBLANK([Rate6]),[Rate6]="Low")="Low"),"Low","Med") – Kms Mar 10 '14 at 13:50
  • Thanks so much Christophe. The above logic was accepted but the test is always failed and the value of my column set to Med every time. Perhaps I need to test for a positve condition for my first test, COUNTA or similar? Thanks again! – Kms Mar 10 '14 at 13:57
  • @Kms if you used the formula pasted above, it is not correct and should actually be =IF(AND(OR(ISBLANK([Rate1]),[Rate1]="Low"),OR(ISBLANK([Rate2]),[Rate2]="Low"),OR(ISBLANK([Rate3]),[Rate3]="Low"),OR(ISBLANK([Rate4]),[Rate4]="Low"),OR(ISBL‌​ANK([Rate5]),[Rate5]="Low"),OR(ISBLANK([Rate6]),[Rate6]="Low")),"Low","Med"‌​) – Christophe Mar 11 '14 at 19:05
  • Thanks again Christophe - I get the following error when I attempt to use that formula. "The formula contains a syntax error or is not supported." I am on SharePoint 2010. – Kms Mar 13 '14 at 12:49
  • Oops, I had an extra space in there. It worked. Thanks again! – Kms Mar 13 '14 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.