Calculating average while ignoring text and assuming blank cells are 0

[johannes2008]

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

1

2

3

5

ST

6

 

[Fluff]

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]

Update your profile as suggested by Fluff

Try

Book3
	A	B	C	D	E	F	G	H	I	J
1
2	1	2	3		5	ST	6		3.4
3
4
5
Sheet1

Cell Formulas
Range	Formula
I2	I2	=AVERAGEIFS(A2:G2,A2:G2,"<>",A2:G2,"<>0")

 

[Fluff]

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

 

[Sufiyan97]

ohh missed that line

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

 

[Sufiyan97]

Another Try

Book3
	A	B	C	D	E	F	G	H	I	J
1
2	1	2	3		5	ST	6		2.833333
3
4
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(A2:G2)/(COUNTBLANK(A2:G2)+COUNT(A2:G2))

 

[Fluff]

Another option depending on the OP's version

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

 

[johannes2008]

Another Try

Book3
	A	B	C	D	E	F	G	H	I	J
1
2	1	2	3		5	ST	6		2.833333
3
4
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(A2:G2)/(COUNTBLANK(A2:G2)+COUNT(A2:G2))

Thanks for the solution. It worked perfectly!

 

[Peter_SSs]

Another option depending on the OP's version

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

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

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