Divide by count of months in cube pivot

[CJHurtig]

Spreadsheet chooses a date of fiscal year. Can have 1 to 12 months in it. The grand total is the last column and then I want to add a calculated Year to Date average. The grand total keeps getting summed into my calculated average unless the sheet has 12 months in it.

[GeneralDisarray]

any chance you can upload an example?

[CJHurtig]

Hello, I have updated an example from our test server. So this sheet has five months in it. The Average column is a calculated
field outside of the pivot table. So when we are attached to the olap cube, by using a fiscal or calendar date for a specific year, the report adds the mew monthly column automatically and the Grand total columns moves to the right each month.

I tried using the calculation below but I don't have the count correctly. I need it to count the number of months, in this example
I want to divide by 5 months. Can you tell me how to do that?



=SUMIF(B8:M8,"<>0")/COUNTIF(B8:M8,">=0")

[GeneralDisarray]

Two problems - you are summing the values from columns B through M - that means you are including the Grand Total. Including the grand total is a problem in both your numerator and denominator.

Try replacing SUMIF(B8:M8,"<>0") with SUMIF(B8:M8,"<>0")/2 - that undoes the problem of including the grand total. Also, you shouldn't need to bother with the SUMIF( RANGE, "<>0") condition because adding a zero doesn't change the value of the sum.

So, your SUM would just be: SUMIF(B8:M8)/2 (again, the 'divide by 2' is just to eliminate the over summing from the grand total row).


You mention you want to divide by 5, but your formula appears to want to neglect months in which the value = 0.

IF YOU DO want to count months with a zero value:
I would just count on the 7th row to get the number of months (use COUNTA() instead of COUNT): COUNTA($B$7:$M$7)-1) - the minus 1 removes the count from the grand total.

Total formula becomes(paste into O8 and copy down): =SUM(B8:M8)/2/(COUNTA($B$7:$M$7)-1)


IF YOU DO NOT want to count months with a zero value:

We just use the 'minus 1' in your COUNTA function to remove the count of the GT column, and since the table is showing zeros as blanks, you have this: =SUM(B8:M8)/2/(COUNTA(B8:M8)-1)

[CJHurtig]

Thank you for your reply!!