Getting average of data with multiple entries for a single date using pivot table in Excel

Question

I have tried this, but I am unsuccessful. I have the data dump in an excel sheet (Excel 2010). The first column has month. The second has the dates. The third column has numerical data. I have applied filters on each column to get the data on the Pivot Table. There are entries for each date. There are sometimes multiple entries for a single date. Now, when I run the Pivot, I get the proper sum, but when I select average, the average is accurate only for the single entries associated with a date. I need to calculate average of multiple data entries for a single date. Here is an example:

November 11/1/13 30
November 11/2/13 25
November 11/3/13 20
November 11/3/13 25

Now, when I run the Pivot table and select to average the data, it calculates the average as (30+25+20+25)/4 = 20 (since there are 4 entries, so the sum gets divided by 4).

This is not desirable.

I require the calculation to be made as taking the 3rd and 4th entry as one for the date 11/3/13. So, the required output is (30+25+(20+25))/3 = 33.33.

I hope the example gives an clear idea of the requirement. I need a solution for this. How do we modify the Pivot so that the average is calculated not based on just the number of entries, but the average is calculated for the number of days.

Answer 1

Create the pivot table described by Ron (with Sum only, not Average), then copy & paste special (values) this pivot table, then pivot that for averages.

Answer 2

Put your dates in the "Row Labels" area. It will Sum and Average by Date

Edit: To get the average of the sum of the daily values, you can either enter an Average formula someplace for the Pivot Table Sum Column, or you could use the following array-entered formula:

=SUM(Values)/SUM(1/COUNTIF(Dates,Dates))

Where Dates is the range where you have your list of Dates (on the original data sheet) and Values is the range where you have your original list of Values.

To array-enter a formula, after entering the formula into the cell or formula bar, hold down while hitting . If you did this correctly, Excel will place braces {...} around the formula.

SUM(1/COUNTIF(Dates,Dates)) will return a count of the Unique dates, so it will return 3 for the dates in question in your example.

Answer 3

This proposed solution uses a working column named: Value (U)

Assuming that your data is located at [B4:B8].
Add a working column at [E4:E8]
Enter this formula at [E5:E8]:

= IF( COUNTIF($C$5:$C5,$C5) > 1, 0,
 SUMIF($C$5:$C$8,$C5,$D$5:$D$8) )

Then use the working column Value (U) to sum and average the values, also add Value (U) as Page Field to filter out the Value (U)=0.