How to get 2003 Excel pivot table to work correctly?

[Peter]

I have created a spreadsheet with 10 years plus of monthly energy consumption
and cost data. I am now trying to create a pivot table which will allow me
to compare monthly usage from one year to the next (Jan 99 compared to Jan 00
to Jan 01, etc) or, alternatively--using the Page field--to filter the report
by Average Daily Temperature. What I would like to see is the month/year in
Column A, KwH for that month in the same row in Column B, and Gas (in units
of CCF--Hundred Cubic Feet) in the same row in Column C just for starters.
What I am getting, however (either using the Layout wizard or not) is Date in
Column A (header on Excel line 4 with the data continuing down correctly in
Column A), BUT when I put KwH in the Column header field, it puts the KwH
header in cell B3 all right but then puts all the KwH data--which it arranges
in ascending order--on line 4 next to the Date header in cell A4.
Furthermore, when I try to enter something in the drop data field, the best I
get is a count of the item and not the data itself, and, at worst, I can't
get anything at all to work in the data field section and end up abandoning
the whole thing and starting over since Excel will not build the table any
further.

How can I get the pivot table to built properly, not give me "Count"
information when what I really want is the consumption data from the
spreadsheet, and organize it in a useable way? I would appreciate hearing
from anyone who can help me get past this logjam.

Many thanks.

 

[mubashir aziz]

Sample file will help to understand your problem and for suitable
solution ......

 

[Peter]

Sample file will help to understand your problem and for suitable
solution ......



mubashir aziz
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=95636

Hello Mubashir,

Sorry for the delay in getting back to you but I have made some progress
(but not enough, I'm afraid) and will attempt to attach a sample file called
PSE pivot example.xls which will illustrate three years worth of data and
what I have been able to do with a pivot table so far. I really don't want
the % average of KwH but rather the % difference of KwH from one month to the
next. It seems that Excel 2007 allows this but Excel 2003 does not??
Whenever I try the field settings to try and make this happen, I get #NA in
Column C. Also, I would very much like to be able to isolate all the
Januarys, all the Februarys, etc in order to compare usage in each of these
months for the 10-year period in my spreadsheet (or 3-year period in this
subset/example). Again, Excel 2007 (which I don't have) appears to allow
this type of grouping but I can't get Excel 2003 to make it work. Notice
that I had to change the column headings from KwH to KwHRS and $ Electricity
to Electricity $ in order to get this far. Also, why does the pivot table
treat columns like rows in the Layout setup? For example, if I put something
like KwH as a Column, it treats it like a row.

I will now attempt to attach the file.

Thanks for your help.

Peter

P.S. I don't see how to attach a file to this message, so I will copy/paste
the pivot table and spreadsheet below:

pivot table

Avg Temp/Day (All)

Data
Date KwHRS Average of KwH Electricity $
1/1/1999 2110 167.65% $136.00
2/1/1999 1800 143.01% $116.52
3/1/1999 1540 122.36% $99.18
4/1/1999 1180 93.75% $73.21
5/1/1999 1250 99.32% $75.98
6/1/1999 1330 105.67% $80.83
7/1/1999 1220 96.93% $74.16
8/1/1999 850 67.53% $51.72
9/1/1999 1170 92.96% $71.13
10/1/1999 1360 108.06% $85.36
11/1/1999 1140 90.58% $81.99
12/1/1999 1440 114.41% $92.68
1/1/2000 1550 123.15% $100.83
2/1/2000 1570 124.74% $103.15
3/1/2000 1580 125.54% $103.83
4/1/2000 1120 88.99% $70.55
5/1/2000 1150 91.37% $70.97
6/1/2000 1050 83.43% $64.82
7/1/2000 950 75.48% $58.65
8/1/2000 410 32.58% $27.14
9/1/2000 530 42.11% $33.45
10/1/2000 1300 103.29% $82.47
11/1/2000 1210 96.14% $70.57
12/1/2000 1410 112.03% $81.86
1/1/2001 1870 148.58% $124.28
2/1/2001 1260 100.11% $83.32
3/1/2001 1350 107.26% $89.52
4/1/2001 1230 97.73% $79.24
5/1/2001 1200 95.34% $75.48
6/1/2001 1160 92.17% $72.97
7/1/2001 1080 85.81% $65.52
8/1/2001 1100 87.40% $68.99
9/1/2001 820 65.15% $50.86
10/1/2001 1140 90.58% $73.17
11/1/2001 1390 110.44% $92.36
12/1/2001 1490 118.38% $98.83


Spreadsheet

ENERGY CONSUMPTION/COSTS EXAMPLE

Date No. of Days Avg Temp/Day KwH $ Electricity CCF BTU Factor Therms $
Gas $ Totals
Jan-99 30 41 2,110 136.00 252 1.1040 278.21 148.48 284.48
Feb-99 33 43 1,800 116.52 276 1.1030 304.43 161.64 278.16
Mar-99 30 44 1,540 99.18 220 1.1180 245.96 131.52 230.70
Apr-99 28 47 1,180 73.21 154 1.1080 170.63 92.73 165.94
May-99 28 52 1,250 75.98 123 1.0930 134.44 74.10 150.08
Jun-99 33 59 1,330 80.83 90 1.0790 97.11 54.84 135.67
Jul-99 33 63 1,220 74.16 56 1.0750 60.20 35.78 109.94
Aug-99 29 67 850 51.72 24 1.0620 25.49 17.86 69.58
Sep-99 30 66 1,170 71.13 51 1.0610 54.11 32.63 103.76
Oct-99 33 56 1,360 85.36 115 1.0870 125.01 69.25 154.61
Nov-99 28 50 1,140 81.99 147 1.1010 161.85 96.01 178.00
Dec-99 30 44 1,440 92.68 205 1.1160 228.78 142.05 234.73
1999 Total 365 nm 17,429 $1,038.76 1,713 nm 1,886.21 $1,056.89
$2,095.65
AVG 30.42 52.67 1,366 $86.56 143 1.0923 157.18 $88.07 $174.64

Jan-00 28 40 1,550 100.83 226 1.1270 254.70 157.60 258.43
Feb-00 33 42 1,570 103.15 269 1.1100 298.59 183.95 287.10
Mar-00 33 43 1,580 103.83 228 1.1010 251.03 155.37 259.20
Apr-00 29 47 1,120 70.55 131 1.0820 141.74 89.73 160.28
May-00 29 52 1,150 70.97 118 1.0680 126.02 80.30 151.27
Jun-00 30 57 1,050 64.82 77 1.0540 81.16 53.42 118.24
Jul-00 32 64 950 58.65 28 1.0540 29.51 22.40 81.05
Aug-00 30 66 410 27.14 0 1.0380 0.00 4.69 31.83
Sep-00 30 61 530 33.45 18 1.0440 18.79 19.28 52.73
Oct-00 31 57 1,300 82.47 67 1.0560 70.75 59.65 142.12
Nov-00 29 48 1,210 70.57 150 1.0812 162.18 130.56 201.13
Dec-00 30 40 1,410 81.86 223 1.1016 245.66 195.36 277.22
2000 Total 364 nm 13,830 $868.29 1535.00 nm 1,680.14 $1,152.31
$2,020.60
AVG 30.33 51.42 1,153 $72.36 128 1.0764 140.01 $96.03 $168.38

Jan-01 33 42 1,870 124.28 245 1.0975 268.89 221.75 346.03
Feb-01 29 40 1,260 83.32 227 1.1005 249.81 249.81 333.13
Mar-01 30 43 1,350 89.52 198 1.0914 216.10 216.73 306.25
Apr-01 31 45 1,230 79.24 161 1.0810 174.04 175.47 254.71
May-01 29 52 1,200 75.48 96 1.0612 101.88 104.66 180.14
Jun-01 31 57 1,160 72.97 66 1.0532 69.51 72.90 145.87
Jul-01 32 63 1,080 65.52 27 1.0424 28.14 32.31 97.83
Aug-01 30 65 1,100 68.99 17 1.0347 17.59 21.96 90.95
Sep-01 30 65 820 50.86 28 1.0413 29.16 32.03 82.89
Oct-01 29 55 1,140 73.17 86 1.0521 90.48 86.17 159.34
Nov-01 29 48 1,390 92.36 155 1.0682 165.57 153.77 246.13
Dec-01 30 44 1,490 98.83 213 1.0780 229.61 211.43 310.26
2001 Total 363 nm 15,090 $974.54 1,519 nm 1,640.78 $1,578.99
$2,553.53
AVG 30.25 51.58 1,258 $81.21 127 1.0668 136.73 $131.58 $212.79


Hope this helps.

Many thanks. Please let me know if there is a way to attach files using
this USENET system.

Regards,

Peter

 

[mubashir aziz]

Hi,

I've read your message. You are newsgroup contributor so can't attach
the file .... I've checked as junior member and can attach the file
easily ...... try to get info from Moderator as i'm very new in this
group and don't know the procedures .... meanwhile i'll try to make a
sample file for you .....

 

[David Biddulph]

This newsgroup is not a binary group, so attachments are not acceptable.

 

[Michael.Tarnowski]

This newsgroup is not a binary group, so attachments are not acceptable.
--
David Biddulph


...

For exchanging files upload the file to one of the free web-hosters
(www,mediafire.com e.g.) and generate a short link by www.tinyurl.com;
this link can be put in a post on this group.
Hope that helps
Michael