Average the hard way..

scelba

New Member
Joined
Nov 17, 2011
Messages
4
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))))))
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
The AVERAGE function ignores text and blanks. How is this new function going to be different?
 

scelba

New Member
Joined
Nov 17, 2011
Messages
4
not intended to be different. Looking to do it the "hard" or "creative" way.
 

scelba

New Member
Joined
Nov 17, 2011
Messages
4
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
COUNT only counts numbers so if you need an alternative to AVERAGE this would suffice

=SUM(range)/COUNT(range)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,462
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top