Help

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

Re: Formula or Rollup

Solved
Jump to Solution
1788 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Christina_Sturr
4 - Data Explorer
4 - Data Explorer

Hi there,

I am trying to set up an OKR table in Airtable.  I have two tables in my workspace.  One for Key Results and one for Initiatives.  Under initiatives I have a column with tasks just check off as they occur.  Please see below.  How do I create a column with a formula that will calculate the % completed based on the check marks listed in the task column.  I have tried to create the formula with creating additional columns (total task count and total task count completed to calculate off of but that didn't work).  I used the "countall tasks" in the total task column and tried to create a formula in the total task count but it isn't working.  

Any guidance would be greatly appreciated.

 

 

 

 

 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

+1 for what Joseph suggested

If operational requirements don't allow for it and you really need to use a long text field, try creating a formula field with this:

 

(
	LEN(
		{Tasks}
	) - 
	LEN(
		SUBSTITUTE(
			{Tasks},
			"[x]",
			"[]"
		)
	) 
)/
(
	LEN(
	  {Tasks}
	) - 
	LEN(
	  SUBSTITUTE(
	    {Tasks},
	    "[ ]",
	    "[]"
	  )
	)
	+
	LEN(
	  {Tasks}
	) - 
	LEN(
	  SUBSTITUTE(
	    {Tasks},
	    "[x]",
	    "[]"
	  )
	)
)

 

 

See Solution in Thread

3 Replies 3
Joseph_Roza
8 - Airtable Astronomer
8 - Airtable Astronomer

Have you considered making each task an individual record instead of including tasks in a long text field?

Especially with the new List view, it should work quite well, and then you can have another table link to the tasks (records), roll them up, and run calculations.

I've built many task managers, and can't say I've ever used long text fields to track tasks. Just checkbox fields and individual records/nesting records.

TheTimeSavingCo
18 - Pluto
18 - Pluto

+1 for what Joseph suggested

If operational requirements don't allow for it and you really need to use a long text field, try creating a formula field with this:

 

(
	LEN(
		{Tasks}
	) - 
	LEN(
		SUBSTITUTE(
			{Tasks},
			"[x]",
			"[]"
		)
	) 
)/
(
	LEN(
	  {Tasks}
	) - 
	LEN(
	  SUBSTITUTE(
	    {Tasks},
	    "[ ]",
	    "[]"
	  )
	)
	+
	LEN(
	  {Tasks}
	) - 
	LEN(
	  SUBSTITUTE(
	    {Tasks},
	    "[x]",
	    "[]"
	  )
	)
)

 

 

OMG, that worked.  Thank you so much!  Thank you also Joseph.  I will keep that in mind and may even try this for this project when I have more time.  However, this worked for me to present to my team as an immediate resolution.  Thanks everyone.  This community is very helpful:)