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

L

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 whe
payback comes, number becomes positive).

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

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

Thanks for any hints
 
D

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

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
 
L

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
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'
causing the problem in the suggested formula....

:mad:
 
D

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

LSB M

Domenic,

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

You really saved me a lot of time... I have no idea how guys like yo
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 th
"CTRL-SHIFT-ENTER" correctly. :
 
Top