Formula help needed using sum, multiplication, division

[Ellen_Edgerton]

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.

[Ben_Young1]

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!