Sum of constant decrement

  • Thread starter Thread starter JV
  • Start date Start date
J

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.
 
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 said:
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?
Click to expand...

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)
 
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
 
Hi. If I am not mistaken,

= 19170 - 2160*yr

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

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

= = = =
HTH
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
 
Back
Top