AVERAGE when some cells contain text

[slam]

I’m having a hard time dealing with an AVERAGE. If B3 is blank, I need “” displayed, otherwise I need it to calculate the average of E3:W3. The problem I’m having is that there is sometimes text in E3:W3. I still want it to include those cells in the average and assume they are 0. How do I go about this? Currently I'm just getting an error.

 

[Scott Huish]

If cells in E3:W3 are blank, do you want to count those as 0 as well?

If so:

=IF(B3="","",SUM(E3:W3)/19)

If not:

=IF(B3="","",SUM(E3:W3)/COUNTA(E3:W3))

 

[slam]

If cells in E3:W3 are blank, do you want to count those as 0 as well?

If so:

=IF(B3="","",SUM(E3:W3)/19)

If not:

=IF(B3="","",SUM(E3:W3)/COUNTA(E3:W3))

The answer would be no, but both those formulas give me the same result. I should have mentioned that the cells in the range E3:W3 contain a formula, so when they're blank, they actually contain "".

 

[BROWN_RY]

you could also do
=if(iserror(sum(e3:w3),"",if(sum(e3:w3)=0,"",sum(e3:w3)))

you might need parantheses around the 0 but not sure

 

[BROWN_RY]

sorry, misread your question. sub SUM for Average and that should work.

let me know

 

[slam]

EDIT: Hmmm, not showing 0 for rows that contain text and no numeric values actually

 

[slam]

If it helps, the text can only be DNS or DNF (or "" I suppose is considered text too)

 

[Scott Huish]

=if(b3="","",sum(e3:w3)/max(1,count(e3:w3)+sum(countif(e3:w3,{"dns","dnf"}))))

 

[slam]

=if(b3="","",sum(e3:w3)/max(1,count(e3:w3)+sum(countif(e3:w3,{"dns","dnf"}))))

Excellent - that works perfectly! Thanks so much!

 

[Aladin Akyurek]

=if(b3="","",sum(e3:w3)/max(1,count(e3:w3)+sum(countif(e3:w3,{"dns","dnf"}))))

To allow for more text...

=if(b3="","",sum(e3:w3)/max(1,count(e3:w3)+countif(e3:w3,"?*"))