Search code examples
google-sheetsformula

Where to place "IFERROR" and "SWITCH" in a formula

I'm working on a personal project, this is a continuation of this post here on reddit.

I'm struggling with the IFERROR and SWITCH functions. Why doesn't this formula work?

=AVERAGEIFS(J5:J,H5:H,A40,I5:I,A26(IFERROR(SWITCH(J5:J,"❤️",10,"💩",0),J5:J)))

Document

What I'm trying to do:

  • Make the average per platform and if it's a movie or a show and including the emojis as value. This is what I asked in my previous post.

  • One question I have is: How can I make the average for a platform including everyone if someone hasn't seen something on one platform?

Solution

  • Added formula to your sheet Cell B42

    =AVERAGE(IFNA(FILTER(IFERROR(SWITCH(J5:J,"❤️",10,"💩",0),J5:J),H5:H=A40,I5:I=A42)))
    • Regarding your second query, I'd say to leave the person out maybe from the average score. his stat within the calculation cell would just be blank