Forum Discussion
Solved
Average of cells containing numbers and blanks and text
How to have the average of cells that are mixed with numbers and empty and text to ... So the function only consider numbers and return the average only of the numbers ? Note, the cells are not nex...
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 Would a regular AVERAGE function not work?
- Also the cells are not next to each other, so you cant select them at once, you have to select one by one
Your support is appreciated - Regular average will count the cells when finding the average, regardless of their value, i want excel to have a formula where if the cell is empty not to count it when finding the average of other cells....
E.g. i have 4 cells ..1 empty and 1 with text and other 2 with numbers for example 100 and 100.... The simple average function if i do it it will return these cells result as 50 instead of 100!! Because it divided on 4 instead of 2.Shadysalman Not sure I follow. When I do exactly as you describe I get this:
the first picture shows the formula result as 100% despite it has 0% in more than once.... and instead it should be 66%.
and the 2nd picture shows the average as 39 % where it should be in fact 74% if the average did not consider the empty cells.
please let me know if you know a solution.
Thanks for the support.
