Trying to illustrate a series of data into percentage

[keveitel]

I am trying to create a table and graph that illustrates the age of my equipment in a percentage value.

Specifically, I want to show:

1) That I have 29 Pieces of equipment that is between 30 and 40 years old as a percentage.
2) That I have 19 Pieces of Equipment that is between 20 and 29 Years old as a percentage.
3) That I have 20 Pieces of Equipment that is between 1 and 19 years old as a percentage.

Nomenclature	Manuf Date	Equipment Age	Percentage
Holding CAM Device	1978	40
Machine, Grinding	1986	32
Horizontal Honing Machine	1986	32
Machine, Grinding	1986	32
750 Ton Hydraulic Press	1987	31
N2 Trailer	1987	31
N2 Trailer	1987	31
Oven, Dry Rod	1987	31
Lathe, Automatic	1987	31
Lathe, Automatic	1987	31
Automatic Lathe	1987	31
Balancing Machine	1987	31
Balancing Machine	1987	31
Balancing Machine	1987	31
Hydraulic Forcing Press	1987	31
Electrode Oven	1987	31
Varnish Dip Tank	1987	31
Lathe, Automatic	1987	31
Boring / Turning Machine	1987	31
Firing V Test Stand	1987	31
Dynamometer	1987	31
Dynamometer	1987	31
Impregnation System	1987	31
Hydraulic Test Stand	1988	30
Bending Machine	1988	30
Forcing Hydraulic Press	1988	30
Boring / Turning Machine	1988	30
MSW Wheel Flange	1988	30
Boring / Turning Machine	1988	30
48" Turret Lathe	1989	29
Rotation System	1989	29
Hydraulic Test Stand	1990	28
Eddy Current Tube Tester	1990	28
Eddy Current Tube Tester	1990	28
36" Turret Lathe	1990	28
54" Turret Lathe	1990	28
Barge Assembly	1990	28
Barge Assembly	1990	28
Machine, Grinding	1991	27
Dry Rod Oven	1992	26
Voltage Regulator	1992	26
Vets Stands	1992	26
Thermal Dry Oven	1993	25
Testing Machine	1996	22
Tank Cleaning System (Vacuum)	1998	20
Paint Booth	1998	20
Machine, Boring, Jig (VF-4)	1998	20
Tank Cleaning System (Vacuum)	1999	19
Paint Booth	1999	19
Lathe (SL-40)	2001	17
EDM	2003	15
Paint Dispensing System	2003	15
Paint Dispensing System	2003	15
Paint Dispensing System	2004	14
Parts Washer	2005	13
Parts Washer	2005	13
Parts Washer	2005	13
Dehumidifier / Chiller	2006	12
Dehumidifier / Chiller	2006	12
Water Jet Cutting Machine	2008	10
CNC Punch Machine	2009	9
CNC Vertical Melting Machine (VF-9)	2010	8
Lathe	2011	7
Robotic Welding Machine	2011	7
Testing Machine (ADTAS)	2012	6
Testing Machine (ADTAS)	2012	6
Water Jet Cutting Machine	2012	6

<tbody>
</tbody>

 

[BGY23]

I am assuming you want to know the number of pieces of kit that fall into the various age categories. So in your sample you have 20 out of 67 pieces of kit that are in the 1-19 age group. This equates to 29.85%.

To do this, assuming your data starts at A5, tie row)....

Put this formula in D6 and copy down =IF(C6<20,"1-19",IF(C6>=30,"30-40","20-29")) Your age in years must be value not text.
Put your age groups in a table at say H5 to H7 now put this formula in I5
'=COUNTIF($D$6:$D$72,H5)/COUNTA($D$6:$D$72) This will return the percentage for the number of pieces in the age group

Grp	% of pieces
1-19	29.85%
20-29	26.87%
30-40	43.28%
	100.00%

<colgroup><col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1408;"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 4384;"> <tbody> </tbody>

 

[BarryL]

if you're looking for it in the column beside the data you could try the below (taking your data being in columns A-C, put this in D2)

=CHOOSE(IF(C2>=30,1,IF(C2>=20,2,3)),COUNTIF(C:C,">="&30),COUNTIFS(C:C,">="&20,C:C,"<"&30),COUNTIF(C:C,"<"&20))/COUNTA($C$2:$C$68)