# Calculation Percent yes/no in one field

Topic Labels: Data Formulas Integrations
Solved
1819 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I am looking for a way to calculate some percentages in my base.

There are a few things I need to do first:

1. I want to only include records listed as "Budgeted" or "Not Budgeted", not any "N/A" records. (this removes TEST 1 and TEST 4 from my calculations)

2. I also want to only include records with an authorized budget entered, so exclude any that the authorized budget=\$0.00 (this removes TEST 2 and TEST 3 from my calculations)

3. Of those filtered to only budgeted or not budgeted and not \$0.00, I want to count the ones with a remaining budget greater than 0, and count those with remaining budget less than zero, getting a percentage of the two. (in this table you can see there would be 50% on budget, 50% remaining budget is negative.

The end goal is to display the percent of records on budget, the percent of records over budget, and possibly the percent of records with no authorized budget entered.

I know the process would involve another table with lookup fields and rollup fields but I can figure out the steps to make it work...

1 Solution

Accepted Solutions

Ah, I think he may just want the count actually, and so we would need 'COUNT' fields to:
1. Get the total number of records that match the filter (Budget != N/A, Authorized != 0)

2. Get the total number of records over budget (Budget != N/A, Authorized != 0, Remaining < 0)

3. Get the total number of records within budget (Budget != N/A, Authorized != 0, Remaining > 0)

And use a formula field formatted to display as a percentage to get the percentages of both by just dividing them:

4 Replies 4
Community Manager

Hi Kevin,

You do need to use a second table.  I like to call this type of table my "reports" table.  It will have 1 record in it called "Reports" and linked back to every project you have .

Attached is the Budgeted roll up.  Repeat for Not Budgeted and N/A projects (if desired).

Next create your formula based on what you want to calculate (that was a little unclear from your explanation.  In this example, I've calculated the remaining budget as a percentage of the totals of the budgeted and unbudgeted.

Back in your projects table, create a lookup field from your Reporter field.  In my example I looked up the total amount budgeted (\$500).  Now I can create a formula that calculates the % spent of the Total in the lookup field.

Ah, I think he may just want the count actually, and so we would need 'COUNT' fields to:
1. Get the total number of records that match the filter (Budget != N/A, Authorized != 0)

2. Get the total number of records over budget (Budget != N/A, Authorized != 0, Remaining < 0)

3. Get the total number of records within budget (Budget != N/A, Authorized != 0, Remaining > 0)

And use a formula field formatted to display as a percentage to get the percentages of both by just dividing them: