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

[LSB M]

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.

[Domenic]

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 <[email protected]>,
LSB M <[email protected]> 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.

[Harry]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[LSB M]

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

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 <[email protected]>,
LSB M <[email protected]> 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.

[Domenic]

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 <[email protected]>,
LSB M <[email protected]> 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....
>
>

[LSB M]

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.