Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Use a formula to calculate percent complete

Topic Labels: Formulas
Solved
Jump to Solution
2985 2
cancel
Showing results for 
Search instead for 
Did you mean: 
James_Rhoads
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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

image

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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

image

James_Rhoads
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you that worked!