Help

Finding the average of a multi select

Topic Labels: Formulas
785 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Amanda_Hickman1
4 - Data Explorer
4 - Data Explorer

I’m having trouble even getting started. I know how I would do this in a real programming language, but I’m not sure how to use an airtable formula.

image

I have a lookup field that is getting rankings (1-5) from another table. I’d like to average those rankings. The dumbed down version is just to average entries in a multi select.

I would expect to be able to use COUNT() and SUM() for that but I’m not getting anywhere.

3 Replies 3
Amanda_Hickman1
4 - Data Explorer
4 - Data Explorer

I’ve also tried to play with using a ROLLUP, but I’m not getting anywhere and I’m not sure how to ensure that the selections are registered as values.

image

In this screenshot, “Calculated Rank” is just giving the result of VALUE({Rank (from Reviews)}) but it is just stringing them together so two values of 5 show up as 55. “Review Count” is accurately counting records where someone has put in a review. The “Rank Rollup” is just looking for a sum right now but it doesn’t appear to be recognizing the values as numbers, even though VALUE seems to.

image

Is the {Rank (from Reviews)} field a Lookup? If so, does {Rank} field in the Review table a Single Select?

If so, that’s where your VALUE() formula should be. The Review table should have a Formula field that gets VALUE({Rank}) since there will just be the one selection per interview.

Then whatever table you’ve screenshotted would have a Rollup field that gets the sum or average of the Review table’s formula field.

Amanda_Hickman1
4 - Data Explorer
4 - Data Explorer

Okay, that makes sense and seems to be working now.