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.

CodePudding user response：

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.

CodePudding user response：

In Excel, this is really easy: 20% less means you are calculating 80% of the value, which in fact means that you are multiplying the value with 0.8.
As the value can't go below 2, you can simply take the maximum between the calculated value and 2, using the formula:

=MAX(2,0.8*A1)

The result looks as follows:

Have fun!

In order to calculate the sum, you can use the simple formula =SUM(A$1:A2) up to the end, as you can see in following screenshot: