Usgin "% of" with a Custom field

Posted by Thomas on October 26, 2001 8:48 AM

Hello,

I'm trying to do this. I have 5 columns (Argentina, Chile, Uruguay, Mexico, USA) that i use in a pivot table, and i get a table showing

Product
Product1 Sum of Argentina 18
Sum of Chile 5
Sum of Uruguay 14
Sum of Mexico 9
Sum of USA 17
Product3 Sum of Argentina 10
Sum of Chile 8
Sum of Uruguay 14
Sum of Mexico 9
Sum of USA 3
Product3 Sum of Argentina 19
Sum of Chile 10
Sum of Uruguay 13
Sum of Mexico 12
Sum of USA 8

I Created a custom field to get "Total" = Argentina + Chile + Uruguay + Mexico + USA

What i want to do is get the % of each country over the total for each product. For example, product1, Argentina should be 18/63.

The problem is that when i try to use the "% of", the field doesn't show my custom field.... can i do something ?

Thanks

Thomas

Posted by Mark W. on October 26, 2001 9:25 AM

Your data isn't organized properly to get the full
benefit of a PivotTable. Your data should be in
First Normal Form (1NF) which means you can't
have a repeating group. Your 5 columns of
countries is a repeating group. Instead, you
should have a single column labelled 'Country'
and your country values (Argentina, Chile,
Uruguay, Mexico, USA) should be on separate
rows. Once you've reorganized your data getting
'% of Total' can be easily accomplished.

Posted by Thomas on October 26, 2001 9:40 AM

Problem is that there are several more columns, something like Argentina - People, Uruguay - People, in which each column is the answer to a specific question, so that doesn't allow me to change the organization of the data.

Thomas

Posted by Mark W. on October 26, 2001 9:53 AM

Well, then you also have a 'People' repeating group
which needs to be "normalized" as well. So it'd
look like this...

"Country","People"
"Argentina",50000000
"Uruguay",45000000
... etc.

You may want to review "Guidelines for creating a
list on a worksheet" in the Excel Help.

Posted by Mark W. on October 26, 2001 10:08 AM

Just to illustrate the benefits of 1NF...

Using the Mapstats.xls file that comes with Excel
here are the current state populations as a
percentage of total USA population. 1NF rules!!!

Alabama 1.62%
Alaska 0.23%
Arizona 1.54%
Arkansas 0.94%
California 12.13%
Colorado 1.40%
Connecticut 1.26%
Delaware 0.27%
District Of Columbia 0.22%
Florida 5.33%
Georgia 2.70%
Hawaii 0.46%
Idaho 0.43%
Illinois 4.52%
Indiana 2.21%
Iowa 1.09%
Kansas 0.98%
Kentucky 1.47%
Louisiana 1.66%
Maine 0.48%
Maryland 1.93%
Massachusetts 2.31%
Michigan 3.66%
Minnesota 1.75%
Mississippi 1.02%
Missouri 2.02%
Montana 0.33%
Nebraska 0.62%
Nevada 0.55%
New Hampshire 0.43%
New Jersey 3.04%
New Mexico 0.63%
New York 7.02%
North Carolina 2.70%
North Dakota 0.24%
Ohio 4.29%
Oklahoma 1.25%
Oregon 1.18%
Pennsylvania 4.65%
Rhode Island 0.38%
South Carolina 1.41%
South Dakota 0.28%
Tennessee 1.98%
Texas 7.03%
Utah 0.73%
Vermont 0.22%
Virginia 2.52%
Washington 2.06%
West Virginia 0.70%
Wisconsin 1.95%
Wyoming 0.18%