Sheet Summary AverageIF

Good morning,

I'm hoping someone can help me with this AverageIF formula. I am trying to get an average between two columns but only if the location in another column meets certain criteria, this is what i have but i'm getting #UNPARSEABLE


=AVERAGEIF (Location:Location, ="Bonita",[Total Graded]:[Total Graded] [# Titles Processed]:[# Titles Processed]

Essentially i need to divide titles graded by titles processed but only when the location is a specific one.


Hope someone can help me!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Mailynm

    An AVERAGEIF needs one column to Average at the end of your statement, it can't read through both at the same time. You can get an Average of each individual column like so:

    =AVERAGEIF(Location:Location, "Bonita", [Total Graded]:[Total Graded])

    and

    =AVERAGEIF(Location:Location, "Bonita", [# Titles Processed]:[# Titles Processed])


    However, I'm not sure I understand the relationship between the "Total Graded" and the "# Titles Processed" columns. You could Sum the output of those two formulas together then divide by 2, like so:

    =SUM(AVERAGEIF(Location:Location, "Bonita", [Total Graded]:[Total Graded]), AVERAGEIF(Location:Location, "Bonita", [# Titles Processed]:[# Titles Processed])) / 2

    Is this the result you're looking for?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Mailynm

    An AVERAGEIF needs one column to Average at the end of your statement, it can't read through both at the same time. You can get an Average of each individual column like so:

    =AVERAGEIF(Location:Location, "Bonita", [Total Graded]:[Total Graded])

    and

    =AVERAGEIF(Location:Location, "Bonita", [# Titles Processed]:[# Titles Processed])


    However, I'm not sure I understand the relationship between the "Total Graded" and the "# Titles Processed" columns. You could Sum the output of those two formulas together then divide by 2, like so:

    =SUM(AVERAGEIF(Location:Location, "Bonita", [Total Graded]:[Total Graded]), AVERAGEIF(Location:Location, "Bonita", [# Titles Processed]:[# Titles Processed])) / 2

    Is this the result you're looking for?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Mailynm
    Mailynm ✭✭

    Thank you very much for your help!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!