How to Average cells containing formula "=LEFT( )"?

Options
Steve B
Steve B
edited 06/20/22 in Formulas and Functions

Hi, I have tried to average cells containing the formula "=LEFT ( )" and it comes up "#DIVIDE BY ZERO". I also tried "=AVG(CHILDREN(). Thanks and looking forward to your help.

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Steve B

    Without seeing your LEFT formula or your sheet - I will shoot in the dark. It will be helpful if you give the community more information to go on.

    Try wrapping your LEFT function (in the cell where the LEFT function resides) with a VALUE function.

    =VALUE(Your entire LEFT formula, including all the parentheses).

    Does the average now work?

    Kelly

  • Steve B
    Steve B
    Answer ✓
    Options

    Hi Kelly, The average does not work and now comes up with "DIVIDE by ZERO".

    The column formula was updated with your recommendation as follows: =VALUE(LEFT([Statement Ranking / Response (Due 7/8)]@row)). I create the AVG formula "=AVG(CHILDREN([Ranking / Response Number]3:[Ranking / Response Number]13))" for a group of cells in the column formula "=VALUE(LEFT..."

    Thoughts on what to try next?

  • Steve B
    Steve B
    Answer ✓
    Options

    Hi Kelly, I can share my sheet if that helps in identifying the issue? I tried just using SUM of the values selected and it comes up with "0". Thanks,

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Steve B

    Without seeing your LEFT formula or your sheet - I will shoot in the dark. It will be helpful if you give the community more information to go on.

    Try wrapping your LEFT function (in the cell where the LEFT function resides) with a VALUE function.

    =VALUE(Your entire LEFT formula, including all the parentheses).

    Does the average now work?

    Kelly

  • Steve B
    Steve B
    Answer ✓
    Options

    Hi Kelly, The average does not work and now comes up with "DIVIDE by ZERO".

    The column formula was updated with your recommendation as follows: =VALUE(LEFT([Statement Ranking / Response (Due 7/8)]@row)). I create the AVG formula "=AVG(CHILDREN([Ranking / Response Number]3:[Ranking / Response Number]13))" for a group of cells in the column formula "=VALUE(LEFT..."

    Thoughts on what to try next?

  • Steve B
    Steve B
    Answer ✓
    Options

    Hi Kelly, I can share my sheet if that helps in identifying the issue? I tried just using SUM of the values selected and it comes up with "0". Thanks,

  • Steve B
    Options

    Hi Kelly, The correct AVG is now displayed. Your formula was correct and I played around with the AVG formula and removed the "CHILDREN" which for some reason auto selected and now the correct average is displayed. Thanks for your help.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey Steve

    Glad it's working. Let me know if you hit any snags

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!