Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |