nested formula

  • Thread starter Thread starter frances.reese
  • Start date Start date
F

frances.reese

Here is what I am trying to accomplish. We are
preparing to review the performance of many employees
which are broken down by their peer group (VP, Dir, Mgr,
etc.) We no longer have access to the software that was
available to us in the past, thus I am pulling together a
manual process via Excel. We will have a review meeting
with the heads of each dept. At the start of the meeting
each employee is in the "Not Reviewed" category column.
As we discuss individual employees, they will then be
moved to either "Excellent", "Strong", "Satisfactory"
or "Unsatisfactory".

We will then take a look at the employees in each
category and review them against each other to determine
if they really belong in that category, or if they should
be moved to a different category. Once their final ranking
is determined we can then look at the master chart for the
overall picture of where our employee population ranks.
This is based on a pivot table and chart (already created).

MY FORMULA SHOULD SOMETHING LIKE THIS: Match Boy, John(in
master list cell A55) to the table in the cell range
B2:F25 and return the value of the Category (column $B$1
or $C$1 or $D$1 OR $E$1 OR $F$1) where the employee has
been placed (such as Boy, John = "Excellent")
Category Column Labels are:
B1 = Excellent
C1 = Strong
D1 = Satisfactory
E1 = Unsatisfactory
F1 = Not Reviewd

Lookup values are A55:F150
Table to mach the look up value is B:2F50

I cannot thank you enough for your patience and any help
you can provide.

Thanks!

I really cannot thank you enough for your patience and any
help you can provide.
 
Hi there Frances - first a couple of things.

1) I would change your email address that you submit posts with so that you
don't get hit with spam. If you have a look at mine it's fairly easy to see
that you need to remove the words NOSPAM PLEASE to determine my real email
address, thus ensuring that automated spam doesn't reach you, but those who
need to can - although having said that, all responses should be posted to
the newsgroup to ensure continuity of the thread and helping others to
learn.

OK, enough of that and onto the problem at hand!! :)

Instead of your approach, have you considered the following?

COLUMN A COLUMN B COLUMN C
NAME PEER GROUP CATEGORY
Boy Director Excellent
John Senior VP Not Reviewed
etc etc

In Column B you could utilise Validation (go to Data -->Validation and
select List and enter the words that you'd allow, separated by a comma...ie
Excellent, Strong, Satisfactory, Unsatisfactory, Not Reviewed). You could
also set this up to have an in cell dropdown from within Data--Validation
(it's a checkbox setting) so that users don'thave to type these entries in.

You could then very easily do a Sum-If or even a Pivot Chat and Pivot Table
to give you the statistics that you need. For example, for a Pivot chart,
you could use Category as the Category Field and Data, and Peer Group as the
Series to give you a chart that shows the number of employees per category
within each peer group (stacked column works well).

Cheers,
Katherine
 
Back
Top