Calculating different values and percentages in rows

[ColonelK0rn]

I'm trying to calculate the values of entries in cells for associates, that
are grouped by supervisors, and then get the % of the values returned for
that supervisor's associate's results.

Each associate has 2 metrics that they are graded on. Values in the cells
are either Satisfactory or Non-Satisfactory.

Example: Supervisor A has 4 associates. 2 associates are Satisfactory in
both metrics, 1 is sat/non-sat, another is non-sat/sat. Manually calculating
the results, that supervisor would be 75% in the first metric, 75% in the
second.

Problem is that I have 300+ associates to track :[ Help?

 

[Bob Bridges]

You didn't spell it out, but I gather your problem is that your supervisors
and associates are all listed on a single page, right? Like this, maybe:

Douglas Burgess S S
Douglas Simpson S N
Douglas Smith N S
Markwell Barclay S S
Markwell Kim S S
Markwell Ridleey S S
Markwell Whipple S S
Satterwhite Bridges N N
Satterwhite Michaelson S S

....and you want to sum the results for each supervisor. Right?

If so, I can think of two ways to do it. Once assumes the data are already
sorted by supervisor and uses the INDIRECT function to set a boundary on the
functions that calculate the supervisors' percentages. The other assumes the
data aren't sorted and creates an extra two helping columns. It's easier to
understand the second way, but if the sheet has a lot of data (more than a
few hundred rows) it might recalculate faster to use the INDIRECT function
even though it's harder to follow. How many rows in your sheet, and are they
sorted by supervisor?

 

[ColonelK0rn]

Bridges, Joe 100% 100% 100% 100%
Lynch, Brian Sat Sat Sat Sat

Bore, Whata 100% 0% 100% 100%
Phillips, Stu Sat Non-Sat Sat
Sat

Cash, Johnny 50% 50% 75% 100%
Dress, Black Non-Sat Non-Sat Non-Sat Sat
Hair, Curly Sat Sat Sat Sat
Foot, Club Non-Sat Non-Sat Sat Sat
Potter, Harry Sat Sat Sat Sat

The data is grouped by supervisor. I can export the information from
SharePoint to a spreadsheet and group it however I'd like. My problem is
trying to calculate values based off of non-numeric values. I fiddled around
with trying to set an array, since I can use variables, but I couldn't quite
get it figured out.

As it is, I have two data metrics: for the Pre-test, and then a final. I'd
ultimately like to calculate each supervisors' pre-test results, and then the
final test results. Of course, the Sat results are desired, so if the
pre-test is non-sat, and appealed to a Sat result, then that's what the
second metric is calculating.

TIA Bob

You didn't spell it out, but I gather your problem is that your supervisors
and associates are all listed on a single page, right? Like this, maybe:

Douglas Burgess S S
Douglas Simpson S N
Douglas Smith N S
Markwell Barclay S S
Markwell Kim S S
Markwell Ridleey S S
Markwell Whipple S S
Satterwhite Bridges N N
Satterwhite Michaelson S S

...and you want to sum the results for each supervisor. Right?

If so, I can think of two ways to do it. Once assumes the data are already
sorted by supervisor and uses the INDIRECT function to set a boundary on the
functions that calculate the supervisors' percentages. The other assumes the
data aren't sorted and creates an extra two helping columns. It's easier to
understand the second way, but if the sheet has a lot of data (more than a
few hundred rows) it might recalculate faster to use the INDIRECT function
even though it's harder to follow. How many rows in your sheet, and are they
sorted by supervisor?

I'm trying to calculate the values of entries in cells for associates, that
are grouped by supervisors, and then get the % of the values returned for
that supervisor's associate's results.

Each associate has 2 metrics that they are graded on. Values in the cells
are either Satisfactory or Non-Satisfactory.

Example: Supervisor A has 4 associates. 2 associates are Satisfactory in
both metrics, 1 is sat/non-sat, another is non-sat/sat. Manually calculating
the results, that supervisor would be 75% in the first metric, 75% in the
second.

Problem is that I have 300+ associates to track :[ Help?

 

[ColonelK0rn]

Bridges, Joe 100% 100% 100% 100%
Lynch, Brian Sat Sat Sat Sat

Bore, Whata 100% 0% 100% 100%
Phillips, Stu Sat Non-Sat Sat
Sat

Cash, Johnny 50% 50% 75% 100%
Dress, Black Non-Sat Non-Sat Non-Sat Sat
Hair, Curly Sat Sat Sat Sat
Foot, Club Non-Sat Non-Sat Sat Sat
Potter, Harry Sat Sat Sat Sat

The data is grouped by supervisor. I can export the information from
SharePoint to a spreadsheet and group it however I'd like. My problem is
trying to calculate values based off of non-numeric values. I fiddled around
with trying to set an array, since I can use variables, but I couldn't quite
get it figured out.

As it is, I have two data metrics: for the Pre-test, and then a final. I'd
ultimately like to calculate each supervisors' pre-test results, and then the
final test results. Of course, the Sat results are desired, so if the
pre-test is non-sat, and appealed to a Sat result, then that's what the
second metric is calculating.

TIA Bob

You didn't spell it out, but I gather your problem is that your supervisors
and associates are all listed on a single page, right? Like this, maybe:

Douglas Burgess S S
Douglas Simpson S N
Douglas Smith N S
Markwell Barclay S S
Markwell Kim S S
Markwell Ridleey S S
Markwell Whipple S S
Satterwhite Bridges N N
Satterwhite Michaelson S S

...and you want to sum the results for each supervisor. Right?

If so, I can think of two ways to do it. Once assumes the data are already
sorted by supervisor and uses the INDIRECT function to set a boundary on the
functions that calculate the supervisors' percentages. The other assumes the
data aren't sorted and creates an extra two helping columns. It's easier to
understand the second way, but if the sheet has a lot of data (more than a
few hundred rows) it might recalculate faster to use the INDIRECT function
even though it's harder to follow. How many rows in your sheet, and are they
sorted by supervisor?

I'm trying to calculate the values of entries in cells for associates, that
are grouped by supervisors, and then get the % of the values returned for
that supervisor's associate's results.

Each associate has 2 metrics that they are graded on. Values in the cells
are either Satisfactory or Non-Satisfactory.

Example: Supervisor A has 4 associates. 2 associates are Satisfactory in
both metrics, 1 is sat/non-sat, another is non-sat/sat. Manually calculating
the results, that supervisor would be 75% in the first metric, 75% in the
second.

Problem is that I have 300+ associates to track :[ Help?

 

[Bob Bridges]

Ok, that layout looks clear enough. I started by copying the below data into
A2:E12 of a blank spreadsheet; if your data are in different columns you'll
need to modify my formulae accordingly. No doubt there are other ways of
doing it, but here's the method that I came up with. By the way, this method
counts on there being no blank lines within a supervisor group, and at least
one blank line between each group.

We'll start by creating some helping columns:

J2: =IF($A2="","",IF($A1="","H","D"))
I2: =IF($J3="",ROW(),I3)
H2: =IF($J2="H",H3,IF($J1="H",ROW(),H1))
G2: ="R"&$H2&"C:R"&$I2&"C"

You can copy these four formulae all the way down your data. Col J
determines whether this is a blank line between groups (""), or a supervisor
row ("H"), or a subordinate row ("D"). Col I uses that information to list
in every cell the LAST row of the current group. Col H does the same but
lists the FIRST row of every group.

G uses cols H and I to construct a string that describes the range for this
group, but it does it in R1C1 format instead of the more usual A1 notation.
Personally I use R1C1 all the time, but if you're not used to it you can read
more about it in the Excel help, or ask here and I'll explain it. I did most
of this in A1 notation on the assumption that it's what you're used to, but I
couldn't think of a way to do this in A1 notation so I did it the way I'm
used to.

Now we just need to set up the COUNTIF statement for each supervisor. In B2
put =COUNTIF(INDIRECT($G2,0),"Sat")/($I2-$H2+1). This displays the fraction
of "Sat"s in this column for the range in G2; you can copy this to C2:E2 and
it'll show those percentages too. Then copy the same formula to each of the
other supervisor rows to get the same result for them.

If you're not used to the INDIRECT function, all it does is let you
construct a string in some cell that looks like an address or range, and then
get Excel to recognize it as such. Normally Excel assumes the address will
be in A1 notation, so if it's in R1C1 notation you have to add the extra
"FALSE" argument (which I wrote as simply "0").

I didn't actually explain much, here, just showed you the formulae and added
a few bare lines of description. I'm thinking once you put this in your
spreadsheet and look at it a bit, you'll figure out anything that isn't
immediately obvious. But if I rushed over anything too quickly, feel free to
ask more, or (better yet) email me - my address is in my profile.