Forum Discussion
Average of cells containing numbers and blanks and text
Shadysalman Please remove the quotation marks around the percentages as they turn them to texts, no matter what format you have set for the cell.
Shadysalman Replicated your examples with the numbers from the pictures and can not reproduce the incorrect calculation you mention. On the first one, 6 values of 100% plus 3 of 0% will always return an average of 66.67%. If your calculation does not, perhaps the 0% cells are in fact texts containing the string "0%". Then the answer becomes 100%.
On the second one, four times 100%, one 0%, one 25%, one 93% and two blanks return an average of 74%. The only way I can come to an average of 39.33% is by averaging (0+25+93), meaning that all other "numbers" are in fact texts like "100%".
Thanks for spending the time.
I just doubled check all the cells I have linked to the outcome cell, and all of them are percentage type not text. however it worth mentioning that all the cells have predefined formula that return the result in each of them.
See the attached for illustration.
Shadysalman Please remove the quotation marks around the percentages as they turn them to texts, no matter what format you have set for the cell.
- Much appreciated for the solution, was simple one, but seems i need to understand formulas more