Sum of constant decrement
 

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.

Sum of constant decrement
 

Hi,

Try this formula for 2009. C5 has 1,500 and C6 has 15. In D5:D7, 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

"JV" wrote in message
...
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.

Sum of constant decrement
 

"JV" wrote:
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)

Sum of constant decrement
 

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


On 7 Maj, 07:56, JV wrote:
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.

Sum of constant decrement
 

Hi. If I am not mistaken,

= 19170 - 2160*yr

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

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

= = = =
HTH
Dana DeLouis


JV wrote:
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.