1

I want to create a "scorecard" where the user simply selects "yes" or "no" for each field in the list. Then, I want to assign a point value to "yes" or "no values and add the point values together in a calculated field call Total.

"No" values are always zero (0). Here is one of my many iterations - which all fail: =(IF([CEU Completion QTR 1]="yes",5,0))+ (IF([CEU Completion QTR 2]="yes",5,0))

The [CEU Completion QTR 1] and [CEU Completion QTR 2] fields are Yes/No (checkbox) fields. There are other fields but resolving these will clue me in on how to add the other fields. I know there is a limit of 7 nested fields. I have 14 fields I need to assign a point value to and add together for a grand total so I need to avoid more than 7 nested If statements - advice is appreciated.

2 Answers 2

0

There is a trick that you can use to calculate this elegantly. It is based on the fact that boolean values (Yes/No) can be used in numeric calculations and their values are treated as 1 for Yes and 0 for No.

So you could create a calculated column, set it's data type to number and use this as the formula:

[CEU Completion QTR 1]*5 + [CEU Completion QTR 2]*5

You could also add the other fields to the formula and calculate the total value.

1
  • 1
    That worked very well! Thank you. Apr 3, 2020 at 12:55
0

If the fileds are Yes/No (checkbox) fields, you need to just create the caluculated column like this:

=(IF([CEU Completion QTR 1],5,0))+(IF([CEU Completion QTR 2],5,0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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