Formula Based on Specific Text Value within the Same Field

Question

My records have a field called Department and a multiple choice field called "Status". The status choices are Approved, Rejected, New, In Review. I would like to get a total of the records with Approved and Rejected only then divide the Approved by that total to get Percent Approved. I don't want the percentage based on all existing records. I will need this calculation to produce reports by Department. I wasn't sure whether I should create a new field to hold this or use a report formula. Either way I need assistance with the formula itself.
#formulas #reportsandcharts

------------------------------
Noreen Justice
------------------------------

MarkShnierYou · Answer

If your department field is just a multiple choice field, then the best solution by far will be to create a table of departments and set the Key field to be the department name.

Then create a relationship where 1 Department has many Records and for the reference field on the right, select your existing Department field.

Then make some Summary fields, for example # Approved in last 90 days, and # Submitted for approvals in last 90 days. Then do the division to get the % up on the Department record. And make a report on the Department table.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------

Sign up

Login to the community