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

Steve B

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.

Kelly Moore

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

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

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,

Kelly Moore

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

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

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

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

Hey Steve

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

Kelly