MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How long does it take to wipe out my holdings.

Posted by Tim Stadler on January 24, 2002 6:08 PM

I can hold my own in Excel, but I'm stumped here.

I have one value in cell A1. It represents my current holdings for example. In the next column are values representing the how many of my holdings are going to be subtracted weekly from A1. These are weekly "withdrawals", so the cells would be B1 to B52 (representing the years's withdrawals.)

What I need to do is find out how many weeks it would take to deplete cell A1, but I can't think of a formula that works.

Posted by Dan Aragon on January 25, 2002 7:19 AM

Not sure if I'm understanding you completely but how about if you do this...
In column C, cells C1:C52 you can subtract the values in Column B from A1, then count how many "weeks" or cells in Column C until you are <=0, using the Countif formula. Make sense? If not, reply back.

Posted by Chris D on January 25, 2002 1:22 PM

in a cell, would :

=if(a1-(sum(b:b))=0,count(b1:b52),"Balance not zero")

work ?

Your description suggests that there are 52 equal payments in a year, ergo the count will always be 52 ! In the event they aren't always equal, this counts the number of weekly deductions, basically just as Dan suggested


Posted by Chris D on January 25, 2002 1:30 PM

Strike that

with mine, you could make a zillion payments and it would still count them all

Dan's tracks the balance on a payment by payment basis, mine doesn't....