Cash Flow
 

I have read a lot, but still can't figure out the best way to do this:

I have 60 cells aligned horizontally: (Jan-05 to Dec-09)
Above the dates I have a project cash flow.

First I need to report the date at the first occurence of cash flow (a
negative number).

Secondly, I need to report the date at the last occurence of cash flow
(a positive number).

This is so I could set up for the XIRR function. The problem is that
the cash flow appears in various dates and durations based on the
project parameters, so I can't lock the XIRR calc to specific cells.

Any help or guidance is greatly appreciated!

Assuming your dates are in Row 1, from column A to column BJ
Assuming that numbers are in Row 2, and that rows 3 and 4 are clear,
for use as helper rows.

in A3 put =IF(A2<0,A1,"")
in B3 put =IF(AND(B2<0,A3=""),B1,A3)
and formula-drag this to BJ3

in BJ4 put =IF(BJ20,BJ1,"")
in A4 put =IF(AND(A20,B4=""),A1,B4)
and formula-drag this to BI4

In BJ3 is the date of your first negative number
in A4 is the date of your last positive number

Hope that helps and is what you needed.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=390699

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1svpae_1122552315.4489@excelforu m-nospam.com...

Assuming your dates are in Row 1, from column A to column BJ

Snip

You may not be aware that you are actually accessing a public Usenet
newsgroup. Please follow Usenet custom and quote the post you are replying
to. Thank you.