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.

 

[slam]

Sorry, I'm back. I just showed this to my buddy, and he thinks it should function slightly differently. Nothing wrong with your formula. He just wants my requirements to change a little.

Apparently DNS should not be counted in the average, but DNF should be considered 0. Also, there's a third potential piece of text which is DSQ, and that should function the same way as DNF - treated as 0.

 

[Aladin Akyurek]

Sorry, I'm back. I just showed this to my buddy, and he thinks it should function slightly differently. Nothing wrong with your formula. He just wants my requirements to change a little.

Apparently DNS should not be counted in the average, but DNF should be considered 0. Also, there's a third potential piece of text which is DSQ, and that should function the same way as DNF - treated as 0.

Back to Hotpepper's suggesstion.... Replace DNS with DSQ in the formula.

 

[barry houdini]

or an "array formula" to do the same

=IF(B3="","",AVERAGE(IF((E3:W3="DNF")+(E3:W3="DSQ"),0,E3:W3)))

confirmed with CTRL+SHIFT+ENTER

 

[slam]

One final little tweak then I promise I'll stop changing things?

So I'm currently using this:
=IF(B3="","",SUM(E3:W3)/MAX(1,COUNT(E3:W3)+SUM(COUNTIF(E3:W3,{"DSQ","DNF"}))))

I think if there's only DNS or "" in E3:W3, the result should be ""

 

[Scott Huish]

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

 

[slam]

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

That's still giving me a result of 0 for rows that only have DNS or "". Looking for a result of "" for those.

 

[barry houdini]

Try

=IF(B3="","",IF(COUNT(E3:W3)+SUM(COUNTIF(E3:W3,{"DSQ","DNF"})),SUM(E3:W3)/(COUNT(E3:W3)+SUM(COUNTIF(E3:W3,{"DSQ","DNF"}))),""))

 

[slam]

Try

=IF(B3="","",IF(COUNT(E3:W3)+SUM(COUNTIF(E3:W3,{"DSQ","DNF"})),SUM(E3:W3)/(COUNT(E3:W3)+SUM(COUNTIF(E3:W3,{"DSQ","DNF"}))),""))

Perfect! Thanks guys. Sorry I'm such a pain