Sum of constant decrement

[JV]

Hello,

Is there a formula which could answer the following?

Monthly deduction is $1,500 in January of 2009 and drops by $15 per month.
What are the totals for 2009, 2010 and 2011?

Thanks in advance for the help.

 

[Ashish Mathur]

Hi,

Try this formula for 2009. C5 has 1,500 and C6 has 15. In D57, enter
2009, 2010 and 2011. In E5, enter
=SUMPRODUCT((C5-(C6*{0,1,2,3,4,5,6,7,8,9,10,11}))). In E6, enter
=SUMPRODUCT((($C$5-(11*$C$6*(D6-$D$5)))-($C$6*{0,1,2,3,4,5,6,7,8,9,10,11}))).
You can now copy this down to cell E7

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[JoeU2004]

Is there a formula which could answer the following?
Monthly deduction is $1,500 in January of 2009 and drops by
$15 per month. What are the totals for 2009, 2010 and 2011?

Put the following formula into B1 and copy down through B3:

=SUMPRODUCT(1500-12*15*(ROW(1:1)-1)-15*(ROW($1:$12)-1))

Alternatively, you can remove the constants as follows:

=12*(1500-12*15*(ROW(1:1)-1))-15*SUMPRODUCT(ROW($1:$12)-1)

Caveat: The use of ROW(1:1) makes it difficult to move and insert cells.
If that's a concern, you could put 2009, 2010 and 2011 into A1:A3, and enter
one of the following formula into B1 and copy down:

=SUMPRODUCT(1500-12*15*(A1-$A$1)-15*(ROW($1:$12)-1))

=12*(1500-12*15*(A1-$A$1))-15*SUMPRODUCT(ROW($1:$12)-1)

 

[Jarek Kujawa]

w/o using SUMPRODUCT and using formula for sum of arithmetic
progression you might use the following:

A1=100000
A2=1500
A3=15
A4=12 (or number of months generally)

=A4*(2*(A1-A2)-(A4-1)*A3)/2

HIH

 

[Dana DeLouis]

Hi. If I am not mistaken,

= 19170 - 2160*yr

where yr is 1, 2, or 3...etc

Returns:
{17010, 14850, 12690...}

= = = =
HTH
Dana DeLouis