Help setting up Charts

  • Thread starter Thread starter matthewluck1
  • Start date Start date
M

matthewluck1

I have a spreadsheet with the following

Column F, building name

Each row on the spreadsheet is a member of staffs name

In Column I, I have got the number 1 if the member of staff has been
trained. If not there is a 0.

I need a Chart to show the number of members of staff who are trained per
work area. In the chart/ graph the work area should be along the bottom row
X. The number of staff members will be in the Y column.

Please would you be able to give some assistance. When I try to create a
chart the information does not show and it appears to be cluttered. There are
91 members of staff on the spreadsheet.

Any help would be appreciated
 
Hi,

In an empty area, say starting in M1, set up a grid such as this
Building Trained Untrained
B1 4 5
B2 0 2
B3 7 3

In cell N2, where 4 is above, enter the following formula
=SUMPRODUCT(--($M2=$F$1:$F$90),--($I$1:$I$90=1))
In O2, where you see 5, enter the formula
=SUMPRODUCT(--($M2=$F$1:$F$90),--($I$1:$I$90=0))

copy these formulas down as far as you building names go.

Highlight the summary area starting in M1 and going down as far as the
column O data goes. Click the chart wizard and select Column, with the
second sub-type, stacked column. Switch the Series in option buttons to
either Rows or Columns so you get the buildings across the x-axis on the
bottom.

If this helps, please click the Yes button.
 
Thank you so much for your help, that's fantastic!!

Im just trying to figure out how I can re-name the labels on the chart
legend. I want to change Series 1 and Series 2 to a more meaningful name.

Thanks again
Matthew
 
Back
Top