Skip to main content

I am trying to create a quality score for a series of job applications. I have a series of fields returning a count of how many “excellent,” :“good” “poor” evaluations each application received, expressed as a numeric value. ie

Count-Excellent

Count-Good

Count-Fair etc.


First I want to multiply each field by a unique value in order to build up a score, ie if an application got 2 “Excellent” ratings (shown in Count-Excellent as “2”), give it 5 points for each Excellent rating and thus returning a value of 10. And 4 points for each Good, etc. Then I want to add all those values together, producing a combined score, and THEN… divide by the number of reviewers, which is stored in a field called ReviewerCount and is also a number.


Basically I want to do something like


((Count-Excellent * 5) + (Count-Good * 4) + (Count-Fair * 2)) / ReviewerCount


and have it produce a score,


but I seem to be getting hung up on the parenthesis and brackets and whether to use SUM. None of the field names have spaces in them. Not sure what I’m doing wrong but keep getting errors.

Hey @Ellen_Edgerton!


Here’s a version of your formula in a corrected syntax using your field names:


(
{Count-Excellent} * 5 +
{Count-Good} * 4 +
{Count-Fair} * 2
) /
ReviewerCount

It produces something like this:



As long as it fits inside of a curly bracket, the spaces or characters in the field names don’t make a difference in regard to formula syntax.





Nerd things… if you’re curious


Your formula works if you remove the - in your field names.

Airtable sees:

Count-Excellent

as this:

{Count} - {Excellent}


- is an operator and thus treats Count and Excellent as two different fields.


See… Airtable doesn’t require the curly brackets if your field does not contain a space or an operator.


If you were to set your field names to fit the format of CountExcellent, then it works perfectly!


Here’s your formula written in that syntax:


(
CountExcellent * 5 +
CountGood * 4 +
CountFair * 2
)
/
ReviewerCount

Just some notes. There might be some things I didn’t exactly explain correctly, but generally, that’s how the syntax treats fields.

Let me know if you have any questions!


Reply