Topic Labels: Formulas
3111 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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?

2 Replies 2
18 - Pluto

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

4 - Data Explorer

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