Checkbox in formula

[Jodie]

Hi - I've tried following the solutions posted but can't get this to work. I have a series of Yes/No field types. I want to add a formula into the table that will calculate the users progress based on the number of these they've completed versus the number they should have for the type... Past posts indicate I need to use 0 and 1 for the field in the formula. The formula verifies but in a test case that should show 20% done, the formulae doesn't produce an answer. Any hints?

(CASE WHEN CAST([@field:preq1] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq2] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq3] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq4] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq5] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq6] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq7] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq8] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq9] AS BIT)=1 THEN 1 END +
CASE WHEN CAST([@field:preq10] AS BIT)=1 THEN 1 END )/ [@field:rcount]

[CoopperBackpack]

Hello @Jodie,

1) There is no need to cast the Yes/No field as bit.

2) The result is blank because when the checkbox is not checked this returns NULL and the whole result is NULL (blank). To fix this you need to add the ELSE part too.

3) I don`t know what the 'rcount' field's data type (Integer or Number) is and what value is stored there.

Let`s say it is an Integer (for example, 10). In this case, you need to consider that an Integer divided by an Integer produces an Integer. For example: 5/10 = 0.5 but the Integer part is 0. So the Formula result is 0.

If the 'rcount' field has a Number data type the formula should be:
 

(CASE WHEN [@field:preq1] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq2] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq3] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq4] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq5] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq6] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq7] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq8] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq9] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq10] = 1 THEN 1 ELSE 0 END ) / [@field:rcount]

If the 'rcount' field has an Integer data type the formula should be (added  multiplication by 1.0): 

(CASE WHEN [@field:preq1] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq2] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq3] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq4] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq5] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq6] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq7] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq8] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq9] = 1 THEN 1 ELSE 0 END +
CASE WHEN [@field:preq10] = 1 THEN 1 ELSE 0 END ) * 1.0 / [@field:rcount]

Perhaps, you need to multiply by 100 the whole result if you need to produce percentages. 

[Jodie]

Thank you @CoopperBackpack - this worked perfectly. Only changed I made was to CAST [@field:rcount] as a Float to get the decimal