Hello!
I have a table that has multiple single select fields with the options of “Complete”, “In Progress”, and “To Do.” I was wondering if there was a formula to count the number of fields with “Complete” as the selected option and turn that into a percent complete of all the tasks.
Thank you!
Best answer by Kamille_Parks11
You can find the “length” of all your Select field values concatenated together, then subtract that from the length of the values concatenated together if “Complete” were removed. Divide that difference by 8 (since “complete” is 8 characters long). Then divide that result by the number of select fields to get a decimal value which can be formatted as a percentage.
Written out as a formula, that would look like:
(
(
LEN(CONCATENATE({Status 1}, {Status 2}, {Status 3})) -
LEN(SUBSTITUTE(CONCATENATE({Status 1}, {Status 2}, {Status 3}), "Complete", ""))
) / 8
) / 3
+27You can find the “length” of all your Select field values concatenated together, then subtract that from the length of the values concatenated together if “Complete” were removed. Divide that difference by 8 (since “complete” is 8 characters long). Then divide that result by the number of select fields to get a decimal value which can be formatted as a percentage.
Written out as a formula, that would look like:
(
(
LEN(CONCATENATE({Status 1}, {Status 2}, {Status 3})) -
LEN(SUBSTITUTE(CONCATENATE({Status 1}, {Status 2}, {Status 3}), "Complete", ""))
) / 8
) / 3
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
