# Formula for lead conversion percentage?

#1

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?

0 Likes

#2

First 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
``````

0 Likes

#3

Thanks for the in-depth description Justin. It has me well on my way!

0 Likes