Averaging multiple data sets by the data whilst excluding zeros

[devinhr]

Hi

I need some help. I have multiple data sets on a given day which I would like to average by day, whilst excluding blanks and zeros. As follows:

[TABLE="width: 180"]
<tbody>[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]5.26
[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]6.84
[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]5.97
[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]3.39
[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]3.06
[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]3.26
[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2015
[/TD]
[TD="align: right"]3.34
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]1.99
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]0.74
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]1.64
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]1.45
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]2.22
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]2.31
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD="align: right"]2.11
[/TD]
[/TR]
[TR]
[TD="align: right"]7/20/2015
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/29/2015
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD="align: right"]43.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD="align: right"]35.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD="align: right"]32.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD="align: right"]35.69
[/TD]
[/TR]
[TR]
[TD="align: right"]8/7/2015
[/TD]
[TD="align: right"]34.25
[/TD]
[/TR]
[TR]
[TD="align: right"]8/8/2015
[/TD]
[TD="align: right"]33.19
[/TD]
[/TR]
[TR]
[TD="align: right"]8/8/2015
[/TD]
[TD="align: right"]31.63
[/TD]
[/TR]
[TR]
[TD="align: right"]8/8/2015
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
</tbody>[/TABLE]


I would like to use a formula that returns a daily average whilst excluding zeros and blank cells. For example, the daily average for the 19th of July 2015 should be 4.45. I would like to be able to drag and copy this formula across large data sets, resulting in a list of average data sets by day. For example:

7/19/2015 - 4.45
7/20/2015 - 1.78
8/7/2015 - 29.9

etc

Thank you for your help.

 

[peterhinton]

=SUMPRODUCT(--(S4:S105<>0),S4:S105)/(COUNTA(S4:S105)-COUNTIF(S4:S105,0))

Change S4:S105, to suit your range,

let me know if this works for you, the range can then be made dynamic using offset so you can filter for specific dates etc.

 

[peterhinton]

or go for a much simpler method, =AVERAGEIFS(B:B,A:A,A1,B:B,"<>"&0) Where A1 would be replaced with date, or cell reference of the date you wish to average

 

[devinhr]

Peter

The SUMPRODUCT array didn't work, it returned a 5 digit number instead. Also I would prefer to not have to go in and change the data range multiple times in the same array. I have a lot of data to work with and this can be time consuming.

The AVERAGEIFS array worked, but is there a way for me to get excel to spit out a table of averages by day without having to go in and specify a date for every average point (i.e. changing A1)? also, how do I get excel to ignore blanks?

Thanks

 

[Tetra201]

Assuming your data start at row 2, put the following formula in cell C2 and copy down:

=IF($A2 < > $A1,IFERROR(AVERAGEIFS($B:$B,$B:$B," < > 0",$A:$A,$A2),0),"")

P.S.: blanks are ignored.
P.S.2: is your average for 8/7 correct?

 

[peterhinton]

"<>"&0 means does not equal zero, and will exclude the blanks from the averageif.

A1 will be where you have the date saved, change this to where your first date is and then drag in the direction for your other dates, adding $ depending on whether its up or down,

 

[devinhr]

Thank you.

I tried using the array you gave me but excel corrected it to this:
=IF($B7<$B6,IFERROR(AVERAGEIFS($G:$G,$G:$G," < > 0",$B:$B,$B6),0),"")

It returns a blank cell though.

p.s.
B6 is where the first date cell is located on my spreadsheet
G6 is where the first data cell is for the set of data I am looking to average

I have multiple data sets for the same day across different buckets of data so I have used the following to limit the formula based on the way my data is organized (rows 6 to 45):
=IF($B7<$B6,IFERROR(AVERAGEIFS($G6:$G45,$G6:$G45," < > 0",$B6:$B45,$B6),0),"")

this still returns a blank cell.

 

[devinhr]

Peter

This worked so I now have similar averages by date which I can drag across. What did you mean to say with adding the $?

Thank you Peter.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]

7/19/2015​

[/td][td]

5.26​

[/td][td]

5​

[/td][td]

avg​

[/td][/tr]
[tr][td]

2​

[/td][td]

7/19/2015​

[/td][td]

6.84​

[/td][td]

7/19/2015​

[/td][td]

4.445714286​

[/td][/tr]
[tr][td]

3​

[/td][td]

7/19/2015​

[/td][td]

5.97​

[/td][td]

7/20/2015​

[/td][td]

1.78​

[/td][/tr]
[tr][td]

4​

[/td][td]

7/19/2015​

[/td][td]

3.39​

[/td][td]

7/29/2015​

[/td][td]

​

[/td][/tr]
[tr][td]

5​

[/td][td]

7/19/2015​

[/td][td]

3.06​

[/td][td]

8/7/2015​

[/td][td]

35.988​

[/td][/tr]
[tr][td]

6​

[/td][td]

7/19/2015​

[/td][td]

3.26​

[/td][td]

8/8/2015​

[/td][td]

32.41​

[/td][/tr]
[tr][td]

7​

[/td][td]

7/19/2015​

[/td][td]

3.34​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

8​

[/td][td]

7/20/2015​

[/td][td]

1.99​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

7/20/2015​

[/td][td]

0.74​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

7/20/2015​

[/td][td]

1.64​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

7/20/2015​

[/td][td]

1.45​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

7/20/2015​

[/td][td]

2.22​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

7/20/2015​

[/td][td]

2.31​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

7/20/2015​

[/td][td]

2.11​

[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

7/20/2015​

[/td][td] [/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

7/29/2015​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

8/7/2015​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

8/7/2015​

[/td][td]

43​

[/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

8/7/2015​

[/td][td]

35​

[/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

8/7/2015​

[/td][td]

32​

[/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

8/7/2015​

[/td][td] [/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

8/7/2015​

[/td][td]

35.69​

[/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td]

8/7/2015​

[/td][td]

34.25​

[/td][td][/td][td][/td][/tr]
[tr][td]

24​

[/td][td]

8/8/2015​

[/td][td]

33.19​

[/td][td][/td][td][/td][/tr]
[tr][td]

25​

[/td][td]

8/8/2015​

[/td][td]

31.63​

[/td][td][/td][td][/td][/tr]
[tr][td]

26​

[/td][td]

8/8/2015​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[/table]

In E1 just enter:

=SUM(IF(FREQUENCY(A1:A26,A1:A26),1))

In E2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$2:E2)<=$E$1,INDEX($A$1:$A$26,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$1:$A$26),MATCH($A$1:$A$26,$A$1:$A$26,0)),ROW($A$1:$A$26)-ROW($A$1)+1),ROW($A$1:$A$26)-ROW($A$1)+1),ROWS($E$2:E2))),"")

In F2 just enter and copy down:

=IF($E2="","",IFERROR(AVERAGEIFS($B$1:$B$26,$B$1:$B$26,">0",$A$1:$A$26,$E2),""))

 

[devinhr]

Aladin

This worked a treat, thank you!