Hi guys, need some help here...

I have 120 colums across, each representing a month.. (month 1-120).

Each month has a dollar value (starts off mostly negative, then when
payback comes, number becomes positive).

I want to know during which month the CUMULATIVE cashflow hits exactly
X dollars (say, $150K).

I know the second part of this solution uses a HLOOKUP. But what
function can I use to tell it to sum "up to" $150K?

Thanks for any hints.


--
LSB M
------------------------------------------------------------------------
LSB M's Profile: http://www.excelforum.com/member.php...o&userid=36258
View this thread: http://www.excelforum.com/showthread...hreadid=562135

Assuming that B1:DQ1 contains the date, and B2:DQ2 contains the dollar
value, try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER....

=INDEX($B$1:$DQ$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($B2 :$DQ2,,,,COLUMN($B2:$DQ
2)-COLUMN($B2)+1))=150000,0))

Hope this helps!

In article ,
LSB M wrote:

Hi guys, need some help here...

I have 120 colums across, each representing a month.. (month 1-120).

Each month has a dollar value (starts off mostly negative, then when
payback comes, number becomes positive).

I want to know during which month the CUMULATIVE cashflow hits exactly
X dollars (say, $150K).

I know the second part of this solution uses a HLOOKUP. But what
function can I use to tell it to sum "up to" $150K?

Thanks for any hints.

Not sure if I fully understand the question but assuming you are saying that
:-

A1contains 1 , B1 contains 2 and so on all the way across to the 120th
column row 1 contains 3

Then , for each of these columns, in row 2 there is a dollar value- e.g. A1
= -5, B1 = 20 etc.

Then in row, say, 5 you want to see the accumulative sum across the columns

Is this the case ?

If so, then in A5 enter *=Sum($A5.A5)* and copy it across all remaining 119
columns. [This will produce a *running total" ]

If you want to use HLOOKUP to indicate the month where value = 150k you'll
probably have to reverse rows 1 & 2 i.e. put values in row 1 and month
numbers in row 2 (AFAIK HLOOKUP does not work with negative offsets).

HTH

Harry




"LSB M" wrote in
message ...

Hi guys, need some help here...

I have 120 colums across, each representing a month.. (month 1-120).

Each month has a dollar value (starts off mostly negative, then when
payback comes, number becomes positive).

I want to know during which month the CUMULATIVE cashflow hits exactly
X dollars (say, $150K).

I know the second part of this solution uses a HLOOKUP. But what
function can I use to tell it to sum "up to" $150K?

Thanks for any hints.


--
LSB M
------------------------------------------------------------------------
LSB M's Profile:
http://www.excelforum.com/member.php...o&userid=36258
View this thread: http://www.excelforum.com/showthread...hreadid=562135