Excel Average or SumIf function problem 1

[blackduck]

=SUMIF(month,1,shop_sales)/COUNTIF(month,1)

I have just realised this gives me the wrong result when its the middle of the month and only the first two weeks results have been entered into the shop_sales range.

month week_ending shop_sales
1 01-Jan-06 1000.00
1 08-Jan-06 2000.00
1 15-Jan-06
1 22-Jan-06
1 29-Jan-06

The above formula returns 3000.00/5 instead of 3000.00/2.

Please help, it should be obvious but I just cant get anything to work.

K

 

[rmikesmith]

K,

This probably should have been posted to the Microsoft:Office forum. However, try the following in place of your formula:

=SUMIF(month,1,shop_sales)/COUNTIF(shop_sales,">0")


Regards,
Mike

 

[smurfyIAG]

Mike is correct but I am going to take a punt and guess that your table will extend past January and you're going to want to sum each month seperately just by changing the month number?
In this case Once you start getting February data there, Mike's Average calc is going to be incorrect also (January sales weeks / count of all sales weeks).
Try this...
Add a column called Active_Month

[b]month|week_ending|shop_sales|[COLOR=red]Active_Month[/color]|Month_Avg|Average_Sales[/b]
=MONTH(B2)| 1-Jan-06|1000|[COLOR=red]=IF(C2>0,A2,0)[/color]|Jan-06|[COLOR=blue]=SUMIF(month,MONTH(F2),shop_sales)/COUNTIF(Active_month,MONTH(F2))[/color]
=MONTH(B3)| 8-Jan-06|2000|=IF(C3>0,A3,0)|Feb-06|=SUMIF(month,MONTH(F3),shop_sales)/COUNTIF(Active_month,MONTH(F3))
1	    |15-Jan-06|1500|1|Mar-06|2000
1	    |22-Jan-06|3000|1|Apr-06|
1	    |29-Jan-06|2000|1|May-06|
2	    | 5-Feb-06|1000|2|Jun-06|
2	    |12-Feb-06|3000|2|Jul-06|
2	    |19-Feb-06|1500|2|Aug-06|
2	    |26-Feb-06|2000|2|Sep-06|
3	    | 5-Mar-06|2000|3|Oct-06|
3	    |12-Mar-06|	|0|Nov-06|
3	    |19-Mar-06|	|0|Dec-06|

Active_Month becomes your conditional IF calculation.

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[blackduck]

Sean, you guessed right.

Great idea, and it works wonderfully.

Then, instead of having the extra field (ie Active_Month), I put the function into the month field so it now displays 0 if there are no shop_sales or the corresponding month if there are.

Therefore in column A I have, =IF(C2>0,(MONTH(B2)),0)
returns this:
month week_ending shop_sales
1 01-Jan-06 1000.00
1 08-Jan-06 2000.00
0 15-Jan-06
0 22-Jan-06
0 29-Jan-06

and I can keep my average monthly sales formula as:
=SUMIF(month,1,shop_sales)/COUNTIF(month,1)
for Jan, 2 for Feb etc.

thanks again
cheers
K

 

[smurfyIAG]

Great. I didn't want to mess with your "month" column in case it was used anywhere else but using that column as the conditional IF certainly simplifies it.

Sean Murphy
Call Centre Analyst
IAG New Zealand