There has to be a formula for this....
 

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

There has to be a formula for this....
 

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.

There has to be a formula for this....
 

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

There has to be a formula for this....
 

A few questions...

Are you getting some sort of error message? If so, which one?

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

Did you copy and paste the formula from the post and into your
worksheet? If so, remove any hard returns that may have been added to
the formula during the process.

In article ,
LSB M wrote:

Looks like an elegant way of solving it...

However, I can't get it to work! Ok, I feel dumb now...

I parsed it out function by function, I think the part that causes a
problem is the match function.

The syntax for Match is:
Match(lookup_value, lookup_array, matchtype)

is "TRUE" a valid entry for lookup_value?? Still trying to find what's
causing the problem in the suggested formula....

:mad:

There has to be a formula for this....
 

Domenic,

I just wanna say thanks for helping out. Wanted to post this earlier
but things got a little crazy.

You really saved me a lot of time... I have no idea how guys like you
can hammer our formulae so quickly/easily... would take me 10X longer.

Thanks again.

p.s. it worked, it was "user error" as I did not do the
"CTRL-SHIFT-ENTER" correctly. :)


--
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