Calculating number of hours spent per product, with diminishing effort

Question

I want to calculate the number of work hours it takes to produce X. The first X takes 20 hours, but for each X it takes 20% less time. However, it will always take a minimum of 2 hours.

Any help is appreciated.

Answer 1

An individual term of a geometric series is given by

The sum of a geometric series is given by

where in your case a=20 and r=0.8

You can show by taking logs or by trial and error that in your particular case you have

0.8^10 = 0.107374

so when n=11 you can see that the time has diminished to just over 2 hours. After that each rep takes 2 hours. So you have

=a*(1-r_^MIN(C2,11))/(1-r_)+MAX(0,C2-11)*2

for the total.

If you just want the time per item, it's

=IF(C2<=11,a*r_^(C2-1),2)

where a and r_ are named ranges for a and r, and the values of N are in column C.