Simplify Calculations with Multiple Variables

[washburncincy]

Hoping someone can provide some guidance on how to streamline calculations that include multiple variables.

I am trying to calculate results against weighted objectives for multiple associates. Here is a very trimmed down version of the table I have currently:
[TABLE="width: 750"]
<tbody>[TR]
[TD]Associate[/TD]
[TD]Role[/TD]
[TD]Function 1 Count[/TD]
[TD]Function 1 Time[/TD]
[TD]Function 1 Learning Curve[/TD]
[TD]Function 2 Count[/TD]
[TD]Function 2 Time[/TD]
[TD]Function 2 Learning Curve[/TD]
[TD]Function 3 Count[/TD]
[TD]Function 3 Time[/TD]
[TD]Function 3 Learning Curve[/TD]
[/TR]
[TR]
[TD]Assoc1[/TD]
[TD]Rookie[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD].5[/TD]
[TD]C[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Assoc2[/TD]
[TD]Tenured[/TD]
[TD]10[/TD]
[TD].83[/TD]
[TD]X[/TD]
[TD]10[/TD]
[TD].5[/TD]
[TD]X[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Assoc3[/TD]
[TD]Veteran[/TD]
[TD]8[/TD]
[TD].75[/TD]
[TD]X[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]X[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]


There is an acceptable range (floor & ceiling) of performance for each Role/Function/LearningCurve. In the example above, let's say those ranges are as follows:

Rookie/Function1/A = 1-2
Rookie/Function2/C = 4-6
Rookie/Function3/X = 10-15

I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.

I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.

Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.

In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.

Any suggestions are welcome at this point. (...and I understand I may need to provide additional detail)

 

[Rukt]

Hi washburncincy,

I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable...

I think you've hit on a viable option, there. Once you take the plunge and start working with PivotTables, they're an exceptional way to cut through large amounts of data. How open are you to changing the format of your table? For a start, I'd propose the following columns:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Manager[/TD]
[TD]Associate[/TD]
[TD]Role[/TD]
[TD]Function[/TD]
[TD]Count[/TD]
[TD]Time[/TD]
[TD]Learning curve[/TD]
[/TR]
</tbody>[/TABLE]

Then, each row would describe a unique combination of the above. (Thus, it would mean a new row for each function that a given associate performed, rather than three columns per function. On the other hand, since in your example the Veteran devotes most of their time to Function 1, they would only need one row.)

Now let's talk about your calculations. It'd be useful if you could give a bit more information, here. For starters, how do the floors and ceilings relate to the columns above? Are they related directly to each function, or learning curve, or both? Once you've calculated those weighted ranges, what comparisons and other calculations do you make?

Cheers,
Rukt