Forum Discussion
Productivity Variance Analysis
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.
- Hi Joe,
I really thank you for responding.
Yes I am just wondering how can I calculate my stats in excel since dont have time components as such.
2nd - can I get your help to show the calculation with your findings Joe. That would be a great help.
PS - I am unable to reply on your latest response. Probably new to the community so having difficulty.
ThanksMaddy29975 wrote: ``I am just wondering how can I calculate my stats in excel since dont have time components as such.``
Before I consider "how ... in excel" per se, I would first consider how I would define statistics mathematically that take tenure time into account.
I am not comfortable offering guidance about that. In fact, I had offered suggestions in some earlier responses. But I deleted them for lack of confidence, which is why you are unable to respond to them now.
-----
Food for thought.... I do not understand or agree with your calculations for productivity (column J), as well as the calculations in column H through K and the "total" calculations in M5:N16.
I would measure productivity (as I infer your definition to be) as the percentage of workout time that is not core time. That is =1 - F2/E2 or equivalently =(E2-F2)/E2 in G2. It is not =E2/F2.
And instead of the calculations in H:K, I would use =PERCENTRANK($G$2:$G$25, G2, 4) formatted as Percentage to determine how a person's productivity compares with others. That is the percentile of his/her productivity.
And again, I have no idea about what you are trying to "say" statistically with the "total" calculations in M5:N16. The "explanation" in your Dec 10 response did not improve my understanding.
Good luck!
- Hi Joe, things are explained like:
workout time is total work processed and core time is spent on the system.
for an example if my core time is 10 hrs. in a day and have processed only 80 than my productivity is 80% and same way if processed 120 than its 120%. the faster I work more time is saved and lesser I work productivity decreases.
So without going into complex calculations ,
How can I show how much hours saved if productivity is bumped up.
PS.... It appears that you crossposted this question to https://www.mrexcel.com/board/threads/analyze-quartile-wise-stats-tenure-wise.1189778 with more details that might answer some of the statistics questions that I raised. I do not participate in that forum.
JoeUser2004 Hi Joe, thanks for understanding and replying. On another forum I asked to analyze the data from Quartile 2 to Quartile Q3.
How is Individual's productivity against workout in these quartile tenure wise. how much hours is extra worked individually.
and also tried working in my way with data. can it be help for me if I add that file here?Question - 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".
Ans- Per my view its Quartile
Question - 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?
Ans - Yes somewhat.
Question - 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"?
Ans - Workout is total work processed (accumulated time)
Question - 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.
Ans - how an individual or whole team productivity is tenure wise, there might be possibility those who are in the system from quite long are not that much productive to the with lesser time period.
- Hi Joe,
I really thank you for responding.
Yes I am just wondering how can I calculate my stats in excel since dont have time components as such.
2nd - can I get your help to show the calculation with your findings Joe. That would be a great help.
Thanks
