Forum Discussion
Productivity Variance Analysis
Hi Experts,
can someone please help with output I am looking for in the sense of productivity at Quartile 2 and Quartile 3. I have tried with formula to get Productivity at Quartile 2 and 3 and same with Workout at Quartile 2 and 3. I want to create a sample which shows variance on productivity and workout. I have mentioned Productivity Baseline.
I would really appreciate if someone can reply. thanks
11 Replies
Maddy29 Not sure what I'm looking at but I do note one inconsistent formula. In N8 you have
=SUM(K3:K26). Note that the rows have shifted down. I assume you want to sum rows 2 to 25. When you correct that the maximum in N16 perhaps makes more sense.
And perhaps you can clarify a bit more what is that you need help with.
Hi Riny_van_Eekelen thanks for sharing. I have rectified the formula.
I actually did some homework to get tenure wise data to do quartile based analysis on individuals productivity. If we look at the workout at Q2 and 3 its increasing but if compare with productivity at Q 2 & 3 there is slight change in their productivity.
2. On actual productivity there is huge gap of 23.70%.
3. what I am trying to show is who is more productive in the sense of workout at Quartile 2 and 3 tenure wise like <6 months , >6mn<1yr , >1mn<2yr , >2yr.
please confirm if it made sense.
thanks.
Maddy29975 wrote: ``what I am trying to show is who is more productive in the sense of workout at Quartile 2 and 3``
That is really a statistics question, not an Excel question. You should take all non-Excel comments with a "grain of salt" (i.e. be wary).
If I understand you correctly, you write "at Q2" and "at Q3" as if they indicate time periods; i.e. "quarter 2" and "quarter 3".
But that is not what QUARTILE(...,2) and QUARTILE(...,3) are at all. Instead, they are statistical values at which 50% and 75% of the data are less. That is, 50% of the values in column G are less than (or equal to) QUARTILE(G2:G25,2).
So, your various calculations seem to (intend to) determine each person's productivity relative to 50% and 75% of the group. Is that what you really mean?
Be that as it may, it is unclear to me, off-hand, whether your comparisons accomplish that.
PS.... After a few milliseconds of thought (wink), I might simply use PERCENTRANK for that purpose -- off-hand, still.
Moreover, your "total" calculations do not seem to have anything to do with the stated goal, to wit: "who is more productive". So please explain what the "total" calculations are intended to demonstrate.
PS.... For my edification, how is the ratio "workout"/"core time" a measure of "productivity" per se? Is that a standard statistic that PTs use? What is the unit of measure for "workout"?
Again, those thoughts are "off-hand" and "out-of-school". I would have to give it a lot more thought in terms of you goal(s?), only one of which has been articulated ("who is more productive"). And I would need an explanation of how the Q2 and Q3 statistics play into those goals; especially, the "max" calculations in columns H and I (see below).
Not very helpful, I know. Sorry.
-----
FYI, some Excel comments....
1. Replace the constants in column G with formulas of the form =E2/F2.
2. The formulas in column H can be simplified to the form =MAX(G2,$N$2).
3. The formulas in column I can be simplified to the form =MAX(H2,$N$3).
4. The formulas in column J can be simplified to the form =F2*H2.
5. The formulas in column K can be simplified to the form =F2*I2.
6. Not sure why you used IFERROR in columns J and K. If the data in columns E and F might be blank, you might want to wrap IF(F2="", "",...) around each formula in column G:K.
Changes #4 and 5 are derived algebraically from change #1. Since G2=E2/F2, then:
H2*E2/G2 = H2*E2/(E2/F2) = H2*E2*F2/E2 = H2*F2.
