# Calculating average while ignoring text and assuming blank cells are 0



## johannes2008 (Dec 20, 2022)

Hello All

I have been trying to figure this one out using AVERAGEIF and AVERAGEIFS but I can't seem to get it. Simply I have a row of data that is mostly numbers but also contains blank cells and cells with text. I want to calculate an average that assumes the blank cells are 0 value and ignores the text. IE:

The average I am looking for with the below table would be 2.833

1235ST6


----------



## Fluff (Dec 20, 2022)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------



## Sufiyan97 (Dec 20, 2022)

Update your profile as suggested by Fluff

Try

Book3ABCDEFGHIJ121235ST63.4345Sheet1Cell FormulasRangeFormulaI2I2=AVERAGEIFS(A2:G2,A2:G2,"<>",A2:G2,"<>0")


----------



## Fluff (Dec 20, 2022)

@Sufiyan97 that is not giving the result the OP wants.


----------



## Sufiyan97 (Dec 20, 2022)

ohh missed that line


johannes2008 said:


> The average I am looking for with the below table would be 2.833


----------



## Sufiyan97 (Dec 20, 2022)

Another Try 

Book3ABCDEFGHIJ121235ST62.83333334Sheet1Cell FormulasRangeFormulaI2I2=SUM(A2:G2)/(COUNTBLANK(A2:G2)+COUNT(A2:G2))


----------



## Fluff (Dec 20, 2022)

Another option depending on the OP's version
	
	
	
	
	
	



```
=AVERAGE(IFERROR(--A2:G2,""))
```


----------



## johannes2008 (Dec 20, 2022)

Sufiyan97 said:


> Another Try
> 
> Book3ABCDEFGHIJ121235ST62.83333334Sheet1Cell FormulasRangeFormulaI2I2=SUM(A2:G2)/(COUNTBLANK(A2:G2)+COUNT(A2:G2))


Thanks for the solution. It worked perfectly!


----------



## Peter_SSs (Dec 21, 2022)

Fluff said:


> Another option depending on the OP's version
> 
> 
> 
> ...


In case any 'blank' cells actually contain a formula returning ""

```
=AVERAGE(IFERROR(--(0&A2:G2),""))
```


----------

