Supervisor to staff ratio from a nomial

[missydanni]

Hi,

I have been asked to produce a table (a bit like an organisational hierarchy) which shows the ratio of supervisors to staff for every department in my organisation. We have a nominal (which is an excel s/s) which is refreshed on a monthly basis and shows every employee from the top down (approx. 3,500 rows). It contains, amongst other things, the employees department and their position within the organisation, which is a number between 1 & 9.
Joe Bloggs Marketing 1
Fred Smith Marketing 2
Sarah Jones Marketing 3
Steven Ross Sales 4

Is it possible from this to build a pivot table which shows the number of staff at each level which would enable me to calculate supervisor ratios, i have been asked to build something which can idealy be refreshed on a monthly basis alongside the nominal.

I just can't seem to get my head round were to even begin,

Unfortunately I am unable to upload an example of the s/s so hope that this all makes sense?

Thanks in Advance

[Bernie Deitrick]

Assuming your Sheet is named "Nominal" and has Department in column B and level in column C:

On a new sheet, in B1 to J1 enter the numbers 1 to 9
The in A2:A?? enter the various departments:
Marketing
Sales
Manufacturing
etc.

and in cell B2, enter the formula

=COUNTIFS(Nominal!$B:$B,$A2,Nominal!$C:$C,B$1)

and copy down to match the values in column A and then across to column J

Thne you can create ratios using

=C2/B2

Copied over and down.

[missydanni]

Thank you so much, that is exactly what i needed.