SOLVED

# Average of cells containing numbers and blanks and text

Copper Contributor

# 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 next or below each other.

10 Replies

# Re: Average of cells containing numbers and blanks and text

@Shadysalman Would a regular AVERAGE function not work?

# Re: Average of cells containing numbers and blanks and text

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.

# Re: Average of cells containing numbers and blanks and text

Also the cells are not next to each other, so you cant select them at once, you have to select one by one

# Re: Average of cells containing numbers and blanks and text

@Shadysalman Not sure I follow. When I do exactly as you describe I get this:

# Re: Average of cells containing numbers and blanks and text

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.

# Re: Average of cells containing numbers and blanks and text

It looks like you obscure your data.

See picture.

It would be better to get real workbook for inspection.

# Re: Average of cells containing numbers and blanks and text

@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%".

# Re: Average of cells containing numbers and blanks and text

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.

best response confirmed by Shadysalman (Copper Contributor)
Solution

# Re: 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.

# Re: Average of cells containing numbers and blanks and text

Much appreciated for the solution, was simple one, but seems i need to understand formulas more
1 best response

Accepted Solutions
best response confirmed by Shadysalman (Copper Contributor)
Solution

# Re: 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.