Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating different values and percentages in rows
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating different values and percentages in rows
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" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating different values and percentages in rows
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 "Bob Bridges" wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating different values and percentages in rows
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 "Bob Bridges" wrote: 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" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating different values and percentages in rows
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. --- "ColonelK0rn" wrote: 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 ....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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating percentages | Excel Worksheet Functions | |||
Calculating percentages | Excel Discussion (Misc queries) | |||
Calculating percentages | Excel Worksheet Functions | |||
Calculating Percentages | Excel Discussion (Misc queries) | |||
Calculating percentages | Excel Worksheet Functions |