How to Average cells containing formula "=LEFT( )"?
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

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

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?

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

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

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?

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,

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.

Hey Steve
Glad it's working. Let me know if you hit any snags
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!