# Re: How to get an average for multiple columns

3512 0
cancel
Showing results for
Did you mean:
6 - Interface Innovator

I’m helping a friend create a base to be used for tracking scores at local dance competitions but have trouble figuring out how to get a proper average for each record. Each line will have a different dance, and each one will have anywhere from 3-5 scores entered, so what we need is the Average Score column to average the input records. Anyone have suggestions on the best way to do this?

Keep in mind, in this instance there are only 4 judges, other competitions might have 5 and might have 3, so if possible it’d be nice to only average input values, not all 5 columns divided by 5. The correct average here would be 98 but the average of 5 scores (with the 5th empty) would be 78.4 and that would skew the info heavily.

Thanks!

10 Replies 10
14 - Jupiter

The AVERAGE() function will handle this just fine:

AVERAGE({Judge A Score}, {Judge B Score}, {Judge C Score}, {Judge D Score}, {Judge E Score})

It will ignore blank values when averaging.

6 - Interface Innovator

Thank you! I thought it would too, but every time I tried the Airtable formula field kept trying to correct me and close off the formula, and adding extra field names was causing errors. I should’ve just typed it up in full and not let it try to guide me. :stuck_out_tongue: Appreciate it Jeremy!

5 - Automation Enthusiast

I’m trying this myself now, and I have several columns to average (employee evaluation). I’ve tallied them all up as indicated above, and it says “NaN”. I had nothing in the other fields (still configuring), but then after the fact added data in case this was why, and nothing changed. HALP. :slightly_smiling_face:

14 - Jupiter

Are you certain all of your fields are “Number” type fields?

5 - Automation Enthusiast

I feel so silly! Of course that’s why it’s not working! :slightly_smiling_face: I manually made rating options (single select) then later decided to do Average. I’ll make the changes and I’m sure it’ll work. Thanks for the quick response :slightly_smiling_face:

14 - Jupiter

It’s possible to convert those “ratings” into numerical values inside the formula, if you’d prefer that. 🤷🏻‍♂️

5 - Automation Enthusiast

Really? I would! We’re new with Airtable but are loving it! How would I go ahead and do that? If you don’t mind sharing of course :slightly_smiling_face:

14 - Jupiter

@Jenny_King - Edit - I had a mistake in my original post of this formula - hopefully you’ll get notification of this update to it.

Sure - you can follow this pattern in your formula field:

AVERAGE(
{Number Field 1},
{Number Field 2},
SWITCH(
{Single Select Field},
"Good",  1,
"Better", 2,
"Best", 3
)
)
5 - Automation Enthusiast

A bit too complicated to understand for this noob, but thanks for trying :slightly_smiling_face: