Z
ZikO
Hi
My data in table are very simply organised as below:
---------------------------------------------
Res.no City Code Var1 Var2 Var3
---------------------------------------------
1 City1 CD0001 num num num
2 City1 CD0002 num num num
3 City1 CD0003 num num num
4 City1 CD0004 num num num
5 City1 CD0005 num num num
6 City2 CD0006 num num num
7 City2 CD0007 num num num
8 City2 CD0008 num num num
9 City2 CD0009 num num num
10 City3 CD0010 num num num
11 City4 CD0011 num num num
12 City3 CD0012 num num num
13 City3 CD0013 num num num
14 City3 CD0014 num num num
15 City1 CD0015 num num num
---------------------------------------------
etc.
I have created pivot table to count a number of Cities, the average of
num from particular columns, min, max and standard dev.
1. First problem: Excel, for some reasons repeates, a few cities in the
fields in Pivot Table such that Pivot Table looks similat similar as below
-----------------------------------------------
Count average min max std.dev
-----------------------------------------------
City1 5 num num num num
City1 1 num num num num
City2 4 num num num num
City3 3 num num num num
City3 1 num num num num
City4 1 num num num num
-----------------------------------------------
Instead of counting properly for instance City1 and City3 etc.
-----------------------------------------------
Count average min max std.dev
-----------------------------------------------
City1 6 num num num num
City2 4 num num num num
City3 4 num num num num
City4 1 num num num num
-----------------------------------------------
Any idea what could be wrong? I tried to remove all blank spaces and
blank characters in city names but it does not help.
2. The second problem I found is that I don't know how to apply Pivot
Table for Filtered Source Data (using Filter from Data Tab). Pivot Table
does not seem to include the data would be reduced due to for example
excluding zeros etc.
I will be very grateful for any suggestions.
Regards
Zbigniew
My data in table are very simply organised as below:
---------------------------------------------
Res.no City Code Var1 Var2 Var3
---------------------------------------------
1 City1 CD0001 num num num
2 City1 CD0002 num num num
3 City1 CD0003 num num num
4 City1 CD0004 num num num
5 City1 CD0005 num num num
6 City2 CD0006 num num num
7 City2 CD0007 num num num
8 City2 CD0008 num num num
9 City2 CD0009 num num num
10 City3 CD0010 num num num
11 City4 CD0011 num num num
12 City3 CD0012 num num num
13 City3 CD0013 num num num
14 City3 CD0014 num num num
15 City1 CD0015 num num num
---------------------------------------------
etc.
I have created pivot table to count a number of Cities, the average of
num from particular columns, min, max and standard dev.
1. First problem: Excel, for some reasons repeates, a few cities in the
fields in Pivot Table such that Pivot Table looks similat similar as below
-----------------------------------------------
Count average min max std.dev
-----------------------------------------------
City1 5 num num num num
City1 1 num num num num
City2 4 num num num num
City3 3 num num num num
City3 1 num num num num
City4 1 num num num num
-----------------------------------------------
Instead of counting properly for instance City1 and City3 etc.
-----------------------------------------------
Count average min max std.dev
-----------------------------------------------
City1 6 num num num num
City2 4 num num num num
City3 4 num num num num
City4 1 num num num num
-----------------------------------------------
Any idea what could be wrong? I tried to remove all blank spaces and
blank characters in city names but it does not help.
2. The second problem I found is that I don't know how to apply Pivot
Table for Filtered Source Data (using Filter from Data Tab). Pivot Table
does not seem to include the data would be reduced due to for example
excluding zeros etc.
I will be very grateful for any suggestions.
Regards
Zbigniew