Average the hard way..

[scelba]

Looking to for a solution to completely replace the AVERAGE function.
New formula needs to handle blanks and texts as not to use them in the division for the average.

thoughts have been SUMMPRODUCT with -- for count and also trying to use COLUMNS - COUNTA to modify the count for division.

getting stuck putting it all together.. assistance in completing this creative solution would be appreciated.

example input cells to average
=B23 0.85 =C23.84 0.95 =D23 rrr

=AVERAGE($B$23:H23)


works for blanks but not inbetween blanks
=SUM($B$23:H23)/(((SUMPRODUCT(--($B$23:H23<>"")))-((COLUMNS($B$23:H23)-(COUNTA($B$23:H23))))))


will not handle any blanks
=SUM($B$23:E23)/(((SUMPRODUCT(--($B$23:E23<>"")))-((COLUMNS($B$23:E23)-(COUNTA($B$23:E23))))))

 

[shg]

The AVERAGE function ignores text and blanks. How is this new function going to be different?

 

[scelba]

not intended to be different. Looking to do it the "hard" or "creative" way.

 

[scelba]

ok I think I got it. As a "Replacement" for the AVERAGE formula one could use a SUM divided by a COUNTIF which only counts numbers... not blanks or texts.

=SUM($B$23:E23)/((COUNTIF($B$23:E23,">0")+COUNTIF($B$23:E23,"<0")+COUNTIF($B$23:E23,"=0")))

 

[barry houdini]

COUNT only counts numbers so if you need an alternative to AVERAGE this would suffice

=SUM(range)/COUNT(range)

 

[Aladin Akyurek]

ok I think I got it. As a "Replacement" for the AVERAGE formula one could use a SUM divided by a COUNTIF which only counts numbers... not blanks or texts.

=SUM($B$23:E23)/((COUNTIF($B$23:E23,">0")+COUNTIF($B$23:E23,"<0")+COUNTIF($B$23:E23,"=0")))

Equivalence

AVERAGE(X) º SUM(X)/COUNT(X)