Formula for Star Rating that Ignores Blank Cells and O Values

Amy Arnold
Amy Arnold ✭✭✭✭
edited 01/25/21 in Formulas and Functions

I would like to Average the Star Ratings provided by seven people in the Rate Column.


The formula I have in the Rate Column is NOT ignoring blank cells or cells with a 0 value.

The image below shows the hidden columns that are connected to the formula.

=AVG([DH#]@row, [JH#]@row, [RH#]@row, [JM#]@row, [RF#]@row, [KM#]@row, [CY#]@row)

I am not super savvy with formulas. I appreciate any suggestions.

Tags:

Best Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 01/25/21 Answer ✓

    Personally, I would reorganize your columns to move all of the hidden columns to the right, so your numerical outputs would be one block from CY# to RH#.

    Then, you can use

    =AVERAGEIF([CY#]@row:[RH#]@row, >0)

    With your current Sheet structure, something like the below might work but I haven't tested it.

    =AVG(COLLECT(Cunesha@row:[RH#]@row, Cunesha@row:[RH#]@row, AND(ISNUMBER(@cell), @ cell>0))

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • Amy Arnold
    Amy Arnold ✭✭✭✭
    Answer ✓

    @Dan Palenchar The first formula worked perfectly! I moved the columns CY# to RH# into a block and plugged in your formula. Thank you 🙌

    💯 =AVERAGEIF([CY#]@row:[RH#]@row,


Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 01/25/21 Answer ✓

    Personally, I would reorganize your columns to move all of the hidden columns to the right, so your numerical outputs would be one block from CY# to RH#.

    Then, you can use

    =AVERAGEIF([CY#]@row:[RH#]@row, >0)

    With your current Sheet structure, something like the below might work but I haven't tested it.

    =AVG(COLLECT(Cunesha@row:[RH#]@row, Cunesha@row:[RH#]@row, AND(ISNUMBER(@cell), @ cell>0))

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • Amy Arnold
    Amy Arnold ✭✭✭✭
    Answer ✓

    @Dan Palenchar The first formula worked perfectly! I moved the columns CY# to RH# into a block and plugged in your formula. Thank you 🙌

    💯 =AVERAGEIF([CY#]@row:[RH#]@row,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!