Average of Weekly data to exclude 0

[Rastamania]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Week numbers
[/TD]
[TD]Calender week
[/TD]
[TD]index number
[/TD]
[TD]Date
[/TD]
[TD]production moisture
[/TD]
[TD]average moisture
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]43466
[/TD]
[TD]1-Jan-2019
[/TD]
[TD]8.54
[/TD]
[TD]=AVERAGEIF($A$2:$A$10,B2,$E$2:$E$10)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]43467
[/TD]
[TD]2-Jan-2019
[/TD]
[TD]8.46
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]43468
[/TD]
[TD]3-Jan-2019
[/TD]
[TD]8.41
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]43469
[/TD]
[TD]4-Jan-2019
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]43470
[/TD]
[TD]5-Jan-2019
[/TD]
[TD]8.39
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]43471
[/TD]
[TD]6-Jan-2019
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]43472
[/TD]
[TD]7-Jan-2019
[/TD]
[TD]8.38
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]43473
[/TD]
[TD]8-Jan-2019
[/TD]
[TD]8.42
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]9
[/TD]
[TD]43474
[/TD]
[TD]9-Jan-2019
[/TD]
[TD]8.43
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi MrExcel,
I have been working on copper production moisture calculations and would like to pick up average moisture for weekly production by using this formula;
=AVERAGEIF($A$2:$A$10,B2,$E$2:$E$10)
The formula gives me the average moisture for a week (7 production days) but when it comes to 0 number, it brings down the average low. Please help me with the formula I use above to exclude 0.

I have tried, ">0" in the formula as =AVERAGEIF($A$2:$A$10,B2,$E$2:$E$10,">0") but it gives me error.

Please rescue me out from this.

Thank you,
Rastamania.

 

[steve the fish]

Hi. In your example that you have provided the blanks will be ignored by the AVERAGEIF so will be giving you the correct result. If you actually have 0 in those cells then use AVERAGEIFS rather than AVERAGEIF. You can then add the 2nd criteria of >0.

=AVERAGEIFS(E2:E10,A2:A10,B2,E2:E10,">0")

 

[Rastamania]

Hi. In your example that you have provided the blanks will be ignored by the AVERAGEIF so will be giving you the correct result. If you actually have 0 in those cells then use AVERAGEIFS rather than AVERAGEIF. You can then add the 2nd criteria of >0.

=AVERAGEIFS(E2:E10,A2:A10,B2,E2:E10,">0")

++++++++++++

Thank you very much for the help.
This has really help me out from my head ach.

Really appreciate your help.

Once more, thank you very much.