I’m fairly new to Airtable and very new to formulas in Airtable. I have a table to track leads. In that table, I have a field for status titled “Converted Lead?”. The converted lead field contains the drop down choices yes, no and not yet. Is there a way to see lead conversion percentage - a formula that will add the No’s and the Not yet’s and then divide them by the yes’s to get our conversion percentage?
Formula for lead conversion percentage?
+21First off, you’ll need to make a new table to do the processing. I typically call this table [Control]. In this table you’ll only have a single record. For simplicity, the primary field value of this record is just a period ("."). Back in your main table, you’ll add a Link field, point the first record to this lone record in [Control], then drag-fill that across all of the other records. Here’s what that might look like:
Whenever you add a new record to your primary table, you’ll need to also add this link, or it won’t count as part of the later calculations.
In the [Control] table, add a Rollup field:
That will give you something like this:
Make a new formula field named {Yes}, using this formula:
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Rollup & "", "Not Yet", ""), "No", ""), "Yes", "Y"))
In short, this removes each “Not Yet” and “No”, replaces each “Yes” with “Y”, then counts the character length of the result. The three “Yes” entries become “YYY,” which is a length of 3.
Repeating this process with modified versions of that formula to make {No} and {Not Yet} fields, we have this:
One final formula field does the math you described above:
(No + {Not Yet}) / Yes
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.