Total time spent over a term where monthly time reduces

[dave0109]

Hi

I'm looking for a formula that will automatically calculate the amount of time required over a term.

Value in the 1st month will be at its highest, subsequent months should reduce by 10% on the previous month ideally to a floor value per month e.g.

M1 10
M2 9
M3 8
M4 7
M5 6
M6 5
M7 5
M8 5

the floor would be 5 but I'd like to set the monthly reduction to be a % rather than the -1 i've used above.

Any ideas very welcome

Thanks

Dave

[XLent]

a sample file may help to better illustrate your requirements, you can attach via GoAdvanced --> Manage Attachments, however if we assume B1 holds your month 1 value, 10, then perhaps:

Please Login or Register  to view this content.

if you prefer a rounded number

Please Login or Register  to view this content.

modify the 2 above to however many dps you want to use.

[dave0109]

a sample file may help to better illustrate your requirements, you can attach via GoAdvanced --> Manage Attachments, however if we assume B1 holds your month 1 value, 10, then perhaps:

Please Login or Register  to view this content.

if you prefer a rounded number

Please Login or Register  to view this content.

modify the 2 above to however many dps you want to use.

I don’t have a monthly set of numbers to work from, rather;

Time taken Month 1
Number of months in contract
Time reduction month on month by 10%

[XLent]

again, a sample file would help but if

A1 = duration of month (10)
B1: =no. of months (12)

with a Minimum cap set at 5, then total time can be calculated as

Please Login or Register  to view this content.

however, there will be a way of avoiding the double evaluation {for sake of applying the MIN cap} - just can't think of it at present...