Cells not reading as numbers when average is used. Giving #VALUE! error.

Hi all.

I have a set of 10 cells, say A2 thru A11, that have random numbers anywhere from 0 to around 20. I run an average with the AVERAGE function to get the average, which it does. Then I have in say B2, the formula =SUMPRODUCT(--MID(M15,ROW(INDIRECT("1:"&LEN(M15))),1)) to give me a sum total. If it is a single average total of 0-9, it will five me the sum product but it is not recognized as a number. If it is 10 or greater, it gives the #value! error. I have tried formatting the cells, but does not help. Anyone know as to why it's not reading as a number, or what may be going on. I've also tried running the AVERAGE function as an ARRAY, vice-versa and both. None of which works. I know this is something simple I am just missing, but can not for the life of me figure out what it is. It's like it's reading as TEXT, but isn't.

Thank you so much for any help and even looking at this for me!

Hopefully this will make sense and I can find out what the problem with it is.

Thank you very much!

Originally Posted by joeu2004

Use the Formulas > Evaluate Formula feature to step through the evaluation of the SUMPRODUCT formula in C16.

At some point, we see SUMPRODUCT(--{"4";".";"8"}).

--"." causes the #VALUE error.

D16 is actually 4.8. It only appears be 5 due to the cell format (Number with 0 decimal places).

I don't understand why you are summing the digits of the average. But perhaps this does what you need.

Array-enter (press ctrl+shift+Enter instead just Enter) the following formula:

=SUM(IFERROR(--MID(D16, ROW(INDIRECT("1:"&LEN(D16))), 1), 0))

Thank you for the reply and help joeu2004! That worked great as long as it is a single digit cell. When it is a double digit or larger cell though, it still gives the error. You did point out the decimal and that was the problem. I just used the ROUNDUP function before the AVERAGE in the formula and that got rid of the problem, so thank you!

I'm working on a random environment and the average needs to be summed up in my situation to get the end result. With the summed average, I'm predicting around 70% accurately more consistently.