# Help with a date and number list

#### Mark Carver

##### Member
I need help with making a numbered list correlated to a date. Sample worksheet attached. THANKS!

#### Attachments

• 9 KB Views: 8

#### Fluff13

##### Active Member
=IF(OR(B9<\$E\$6,B9>\$E\$6+\$E\$7-1),"",B9-\$E\$6+1)

#### Mark Carver

##### Member
WONDERFUL! That works. THANKS.

#### GraH - Guido

##### Well-Known Member
Since I was on it while making diner ,

Alternative in same logic
=IF(AND(B9>=\$E\$6;B9<(\$E\$6+\$E\$7));N(D8)+1;"")
=IF(OR(B9<\$E\$6;B9>\$E\$6+\$E\$7-1),"",N(E8)+1)

#### Fluff13

##### Active Member
You're welcome & thanks for the feedback.

#### Mark Carver

##### Member
I thought that I could finish my worksheet from the formula that you provided, but no luck. I am trying to make withdrawals over a period of 10 years (in this case) and end in a zero balance. Would you be so kind as to help me one more time? THANKS

#### Attachments

• 13.8 KB Views: 4

#### Mark Carver

##### Member
Since I was on it while making diner ,

Alternative in same logic
=IF(AND(B9>=\$E\$6;B9<(\$E\$6+\$E\$7));N(D8)+1;"")
=IF(OR(B9<\$E\$6;B9>\$E\$6+\$E\$7-1),"",N(E8)+1)
Thanks. If you have a chance, could you look at the additional question on this same subject? I put another worksheet in my reply.

#### GraH - Guido

##### Well-Known Member
Not sure I understand completely, but I get you want a countdown, correct?
Try =IF(OR(F9<1,F9>\$J\$5),0,\$J\$5-F9)

#### Mark Carver

##### Member
Not sure I understand completely, but I get you want a countdown, correct?
Try =IF(OR(F9<1,F9>\$J\$5),0,\$J\$5-F9)
I will play with this formula. However, to be clear, what I want to do is take a relatively even withdrawal for each of the 10 years. (It is a tax issue.) So, for whichever year the withdrawals start, I want even amounts until the balance is depleted at year 10 (in this case). My dilemma is that for each year, the balance earns interest so I have to account for that as well. THANKS!

#### Mark Carver

##### Member
I will play with this formula. However, to be clear, what I want to do is take a relatively even withdrawal for each of the 10 years. (It is a tax issue.) So, for whichever year the withdrawals start, I want even amounts until the balance is depleted at year 10 (in this case). My dilemma is that for each year, the balance earns interest so I have to account for that as well. THANKS!

#### Attachments

• 13.8 KB Views: 3

#### GraH - Guido

##### Well-Known Member
Hi Marc, then my formula won't do. As I was afraid, I somewhat misunderstood the point. I think it is more clear now.

#### Fluff13

##### Active Member
Whilst I think I understand whhat you need, I'm afraid I haven't got a clue how to do it.

#### Mark Carver

##### Member
Whilst I think I understand whhat you need, I'm afraid I haven't got a clue how to do it.
Thanks, at least, for taking a look. It has me flummoxed as well. I suppose it is a VBA solution and that's not my skill!

#### Mark Carver

##### Member
Thanks, at least, for taking a look. It has me flummoxed as well. I suppose it is a VBA solution and that's not my skill!
I did figure it out by looking on the web for TVM calcs and the help of another friend. For future reference here is the solution. Again, thanks!

#### Attachments

• 12.7 KB Views: 4

#### Mark Carver

##### Member
Hi Marc, then my formula won't do. As I was afraid, I somewhat misunderstood the point. I think it is more clear now.
See my reply to Fluff, below. Again, thanks for the help.

#### GraH - Guido

##### Well-Known Member
Thx for sharing the solution, Mark. Financial functions is some (one of many ) of my blind spots in Excel...

#### Peter Bartholomew

##### Well-Known Member
This is an array formula version; the formula cells are highlighted in 'Barbie' pink (sorry about that)

The catch is that I have used a function ACCUMULATE written by Charles Williams (MVP) in his FastExcel addin.
Since dynamic arrays treat accumulations (finance corkscrews) as circular references it is a bit of a problem to do this without the ACCUMULATE function. The basic approach is to use SUMIFS to total the amounts to date but allowance also has to be made for the return Rate.

#### Peter Bartholomew

##### Well-Known Member
I decided that it would be better to do without the ACCUMULATE function until such time as we can badger Microsoft into implementing it. It is a major impediment to the adoption of dynamic arrays that accumulation/corkscrews do not work without it. The workaround is to use repeated SUMIFS to aggregate each value from the start. Since I know next to nothing about finance, I worked mainly from first principles.

Because I couldn't meaningfully add the 'Withdrawals' across different years, I based each withdrawal upon its value at 2022, Then I compounded the interest from that date to bring the 2022 value up to year under consideration. For the moment, I haven't bothered about Opening Balances or Returns because they are not needed for the calculation

#### Attachments

• 15.6 KB Views: 3