Pivot Tables Wizard/Problem

[DPIROBERT]

Hi,

I have multiple entries for a single date:

31-July-1995 514.094
31-July-1995 512.125
31-July-1995 509.156
31-July-1995 505.172
31-July-1995 502.203
31-July-1995 502.25
31-July-1995 503.281
01-August-1995 512.406
01-August-1995 512.469
01-August-1995 515.5
01-August-1995 516.563
01-August-1995 517.594
01-August-1995 519.594

And basically, i need to use a pivot table or something similar to break it down to one averaged reading for each of the listed dates, so i can compare it to another column of data which arrived in Average Daily form.
My knowledge of pivot tables has been exhausted (quite quickly, i'll admit) and i dont know to solve the issue.

Any help would be greatly appreciated. Cheers

Rob.

[rylo]

Rob

Say your data is in the range A2:B14, with the headings Date, Value in the range A1:B1.

Select the range A1:B14 and create your pivot table. Put the date in the row field, and value in the data.

At the moment it should be showing the sum of all the values (if you have the default) so you need to change this default action. Right click on one of the totals, select field settings, and change the summarize by to average.

HTH

rylo

[DPIROBERT]

Thanks for responding to my post again rylo

Yep, i've chosen the B column to be averaged, however i've just realised when i changed the date formatting all the values have a time recorded with them as well.
an example of data now looks like:

31/10/1995 13:00 2604.75
31/10/1995 14:00 2603.75
31/10/1995 14:45 2618.75
31/10/1995 16:00 2613.75
31/10/1995 19:00 2626.625
31/10/1995 20:00 2639.625
1/11/1995 0:00 2643.625
1/11/1995 3:45 2650.5
1/11/1995 4:00 2649.5
1/11/1995 8:00 2653.375

This would explain why the averaging doesnt work. Is there a way to 'ignore' the h:mm at the end of the dates so i can average?

[rylo]

Hi

Add another column at the end that only contains the date component. Then use that column in the pivot.

rylo

[DPIROBERT]

Hi,

Ah, i'm not sure how you mean. Pasting special>values when the column is formatted as dd/mmm/yyyy? That results in the hh:mm being pasted as well.

With dates theres the problem with excel being unable to recognise monthly patterns, so i cant enter it semi-manually.

Rob

[rylo]

Rob

Try something like this to remove the time component.

=DATEVALUE(DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1))

This will reduce it all to a date only.

rylo

[arthurbr]

=int(a1) will also remove time

[DPIROBERT]

That formula works a charm rylo!

On a parallel...

I'm trying for annual averages, is there a way to make a cell only recognise the VALUE in another cell, and not regard the formatting in anyway?
ie.
1/3/1906 - in yyyy formatting displays "1906"(however the 1/3 remaind 'embedded' for lack of a better word, and still disrupts the pivot table averaging. Is there a way to make another column next to it only display the "1906"? I've tried modifying the formula you gave me.

=DATEVALUE(YEAR(A2))
However i get a #VALUE! error.
and i tried simpler:
=Value(A2)
and that still retained the "1/3".

[rylo]

Hi

try = year(a2)

rylo