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.

 

[Aladin Akyurek]

Aladin

This worked a treat, thank you!

You are welcome. Thanks for providing feedback.