Finding the average of a multi select

[Amanda_Hickman1]

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.

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.

[Amanda_Hickman1]

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.

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.

[Kamille_Parks]

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]

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