Average of a quarter: How do I get "5" as my answer and NOT 5.933333333?

[VBAProIWish]

Hello,

Using the example below, "Jim" has 15 rebates sent out in quarter 1 of 2011.

7 Rebates in January
6 Rebates in February
2 Rebates in March
15 Rebates total

15 (rebates) / 3 (months) = 5 rebates/month average for the first quarter of 2011.

I'm looking to get an answer of "5" so that I can put this into a pivot table, but can't seem to figure it out.

Sales Item Date Rebate Sent
Jim Rebate 1/1/2011
Jim Rebate 1/1/2011
Jim Rebate 1/1/2011
Jim Rebate 1/1/2011
Jim Rebate 1/1/2011
Jim Rebate 1/1/2011
Jim Rebate 1/1/2011
Jim Rebate 2/15/2011
Jim Rebate 2/15/2011
Jim Rebate 2/15/2011
Jim Rebate 2/15/2011
Jim Rebate 2/15/2011
Jim Rebate 2/15/2011
Jim Rebate 3/22/2011
Jim Rebate 3/22/2011

I tried using a "count of count" formula like this here...

=SUMPRODUCT(--($A$2:$A$16=A5),--($C$2:$C$16=C5))

Salesman Item Date Rebate Sent Count
Jim Rebate 1/1/2011 7
Jim Rebate 1/1/2011 7
Jim Rebate 1/1/2011 7
Jim Rebate 1/1/2011 7
Jim Rebate 1/1/2011 7
Jim Rebate 1/1/2011 7
Jim Rebate 1/1/2011 7
Jim Rebate 2/15/2011 6
Jim Rebate 2/15/2011 6
Jim Rebate 2/15/2011 6
Jim Rebate 2/15/2011 6
Jim Rebate 2/15/2011 6
Jim Rebate 2/15/2011 6
Jim Rebate 3/22/2011 2
Jim Rebate 3/22/2011 2

...but it's giving me an average of "5.933333333"


Can anyone figure my dilemma out?

Thanks

 

[VBAProIWish]

Wow, I think I got this one ironed out fellas, very exciting

I believe I have figured out how to combine your solution with my problem, see below)[/B]

Here's what I did. I selected the data only portion of the data (not the PivotTable data) and clicked on INSERT/(arrow under PivotTable)/PIVOT CHART onto a new worksheet. This gave me 6 different fields in which to choose from...

1. Sales
2. Type
3. Date
4. Month
5. Flag
6. Quarters

I tried many different combinations of putting different fields in the 4 different places...

> Report Filter
> Legend Fields
> Axis Fields
> Values

...but was not able to come up with a way that showed me that Mary has an average of 5 closed rebates in 2011-Q1 and Jim had an average of 5 pending rebates in 2011-Q1.



I also tried ONLY the Pivot Table data and here's what I did with this...



I highlighted ONLY this section here...

Sales Type Years Quarters Total
Jim Pending Rebate 2011 Qtr1 5
Jim Total 5
Mary Closed Rebate 2011 Qtr1 5
Mary Total 5
Grand Total 5

...and then clicked on INSERT/(arrow under PivotTable)/PIVOT CHART onto a new worksheet. This gave me 5 different fields in which to choose from...

1. Sales
2. Type
3. Quarter
4. Years
5. Total

In Axis Fields, I put in Sales, Quarters, Type.
In Legend Fields, I put in Years
In Values Field, I put in "Sum of Total"

I'm have no idea how it works, but it did indeed give me my "5 average per Quarter" for both Jim and Mary.


(AFTER 1 HOUR OF WORKING THIS, BUT IT'S STILL FUN! )


If I combine your input into my issue, it appears that this is what I should do through VBA and/or macro...

1. Add "month" and "flag" column headers in Col's D & E - I can do this.

2. Input the 2 formulas into Col's D & E, I can do this.

3. Highlight all my data + 2 new columns (1,000's of rows) - I can do this.

4. Put this data into a PivotTable - I can do this.

5. Figure out what fields go where, I BELIEVE I have this done...

For other newbies wondering proper field positions to get this working, here's what I did...

> Report Filter - Date, Month, Type
> Axis Fields - Sales, Quarters, Years
> Values - Average of Flag


Thanks so much to all for your assistance with this one. I just might have this one nailed down thanks to you.

If I have more issues, I will re-post my results here, hopefully you guys are on "auto-notify" for this thread lol.

Thanks again!

 

[GlennUK]

I'm glad that my solution helped you so much ... it sounds like you have nailed this now.