Diminishing Returns

[customesp]

Hi All,

I am looking to calculate diminishing returns to answer the following question:

"If I increase the number of hours per week, at what point does it stop having an impact on employee satisfaction"

Thanks,

Weekly Store Hours vs. Forecast	Employee Satisfaction Score		"If I increase the number of hours per week, at what point does it stop having an impact on employee satisfaction"
203​	88​
321​	67​
45​	83​
-45​	56​
432​	78​
34​	77​
54​	45​
87​	74​
-44​	76​
-66​	55​
123​	68​
78​	88​
388​	76​
233​	56​
24​	62​

 

[Legacy 143009]

Interesting question.. Could you please be more explainatory?

78-hour working and 203-hour working employee seems to have the same satisfaction. Which means after 78 hours, has no effect on employee. But tgis is not the case I guess. Could you please give more information about the hours, and how the satisfaction is related to this.

For instance, there is -45 in hours column. What do negative hours mean?

 

[customesp]

Interesting question.. Could you please be more explainatory?

78-hour working and 203-hour working employee seems to have the same satisfaction. Which means after 78 hours, has no effect on employee. But tgis is not the case I guess. Could you please give more information about the hours, and how the satisfaction is related to this.

For instance, there is -45 in hours column. What do negative hours mean?

Hi Flashbond... I'm trying to determine if going over scheduled hours vs forecasted hours in each store (which could be a third column, store 1, store 2, etc) affects the satisfaction score. Negative means that the store used fewer hours than forecasted. The numbers are made up in this example, but I would like to see a calculation to look at both columns and see a clear diminishing return on the number of hours over the forecast at a certain point. For example, going past 50 hours over the forecast had little to no impact on the final satisfaction score increasing. Thanks for any help you can provide.

 

[Legacy 143009]

Hello,

I hope, I got the problem right. I made a setup as below:

As you can see lower and higher satisfaction diminishes as the forecasted hours decrease and increase. My formula calculates the average difference between all satisfaction points. Takes that value and returns the row where the difference is no more higher than the average difference.

In this example, the average difference is 3.8. In this regard, our lower and upper bounds will be the values where the difference is lower than this.
As you can see, below 15 hours, the difference is only 3. So there is no more average benefit after this point.
Also after 235, the benefit is only 3 points which is less than the average -3.8.

To calculate the lower point:

=INDEX($A$2:$A$19,MATCH(AVERAGE($B3:B19-$B2:$B18),$B3:B19-$B2:$B18,1)+1)

To calculate the higher point:

=INDEX($A$2:$A$19,MATCH(1,1/(AVERAGE($B3:B19-$B2:$B18)<$B3:B19-$B2:$B18),1)+1)

Note1: All values must be sorted to make these formulas work. I don't think a solution with arbitrary numbers is possible. You should wait for other answers if you are looking for something different.
Note2: These are array formulas. You should press Ctrl+Shift+Enter together after paste.