Question

I have a database of applicants and three views that allow reviewers to rate them from ⭐ to ⭐⭐⭐⭐⭐. I want to create a formula field that captures their average rating. I can use .length() but I'm wondering if there are other approaches that might work.

Not everyone has rated every applicant so 0 stars doesn't mean that the applicant is a zero, just that the reviewer hasn't gotten to them yet. I'm also not sure how to account for that.

Answer 1

A simple if 0 = No reviews yet in your Notion formula makes the trick.

Answer 2

use conditional logic to include only the values that exist. For example, if you have three reviewer fields (Reviewer 1, Reviewer 2, Reviewer 3), you can use this formula:

(
  if(prop("Reviewer 1") != "", prop("Reviewer 1"), 0) +
  if(prop("Reviewer 2") != "", prop("Reviewer 2"), 0) +
  if(prop("Reviewer 3") != "", prop("Reviewer 3"), 0)
) / max(
  if(prop("Reviewer 1") != "", 1, 0) +
  if(prop("Reviewer 2") != "", 1, 0) +
  if(prop("Reviewer 3") != "", 1, 0),
  1
)

This formula adds only the filled-in ratings and divides by the count of ratings that exist, preventing division by zero errors. If you're using star emojis in Select fields (like “⭐⭐⭐”), you can convert them to numbers using length(prop("Reviewer 1")), then apply the same formula logic to average those values.

Answer 3

I tried to use that formula, but it didn't quite work. Here's what actually worked:

(
  length(prop("R1")) +
  length(prop("R2")) +
  length(prop("R3")) 
 )/(
max(1, 
  if(length(prop("R1")) != 0, 1, 0) +
  if(length(prop("R2")) != 0, 1, 0) +
  if(length(prop("R3")) != 0, 1, 0)
)
 )

Add up the length of each reviewer's value (eg. ⭐⭐ has a length of 2), and then build the denominator by adding one to it for each non-zero review (or using 1 if all reviews are 0)