Finding month when value first <= 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My worksheet has four columns (month, withdrawal, _% return, balance). The purpose is to show monthly balances in a retirement fund with equal monthly withdrawals (beginning of month) and % earnings (end of month). These columns are arranged so that there are four years to a page (top-to-bottom left column, then top-to-bottom right column). The worksheet currently displays 40 years. I want to have a cell near the top that returns the first month (column A or F) when the balance (column D or I) is <= 0. I can scroll down to find it when I change the withdrawal or % return amount, but I'm sure that Excel can find and display it for me. I just don't know how to do it. Any help would be appreciated.
 
=INDEX(A1:A100,MATCH(TRUE,D1:D100<=0,0))

entered with ctrl + shift & enter

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


Dale said:
My worksheet has four columns (month, withdrawal, _% return, balance).
Click to expand...
The purpose is to show monthly balances in a retirement fund with equal
monthly withdrawals (beginning of month) and % earnings (end of month).
These columns are arranged so that there are four years to a page
(top-to-bottom left column, then top-to-bottom right column). The worksheet
currently displays 40 years. I want to have a cell near the top that
returns the first month (column A or F) when the balance (column D or I) is
<= 0. I can scroll down to find it when I change the withdrawal or % return
amount, but I'm sure that Excel can find and display it for me. I just
don't know how to do it. Any help would be appreciated.
 
Thanks so much for your expert help. Working with Excel fascinates me. Unfortunately, I don't have enough time to "play" with it to learn all the neat functions. In my example, can the formula be altered to include columns A and F as the reference and columns D and I as the data columns? I have two sets of four columns on each page. If not, I can work with what you have already given me. Thanks again for the fast response.
 
Back
Top