Try this instead...
The following formula will return the cell value in Row 1 (assumed header
row) for the last cell that has data in Row 3...
=IF(COUNTA(3:3)=0,"",INDEX($1:$1,1,SUMPRODUCT(MAX( (3:3<"")*COLUMN(3:3)))))
Change the three occurrences of 3:3 to whatever row you want (for example,
change them to 5:5 for Row 5). This formula can be copied down if required.
--
Rick (MVP - Excel)
"cardan" wrote in message
...
On Aug 28, 2:34 pm, "Rick Rothstein \(MVP -
VB\)"
wrote:
The following formula will return the cell value in Row 1 (assumed header
row) for the last cell that has data in Row 3...
=INDEX($1:$1,1,MATCH(LOOKUP(2,1/(3:3<""),3:3),3:3,0))
Change the three occurrences of 3:3 to whatever row you want (for example,
change them to 5:5 for Row 5). This formula can be copied down if
required.
Rick
"cardan" wrote in message
...
Hello all,
I am working on some cash flows and I am trying to find a formula that
will return the month with the last value in it.
For example; I have a header row with 24 "months" in it. (each cell
equals a sequential month, year) below that I have a set of cash
flows. These cash flows can either go out 2 months, 15 months, 24
months, etc..) I am trying to write a one-cell formula that will find
the last month with a value and then return the month (the header). I
have a formula that looks in the column above and looks at the sum of
the remaining months. It looks something like this.
=IF(AND(above cashflow0,SUM(above cashflow-over 1:end
cashflow)=0),date,"-")
This works, however, I have to have create another row to calculate
every cell. I would like to just have one cell to tell me the last
month. Is this possible?
Thank you for you time.
Cheers,
Dan- Hide quoted text -
- Show quoted text -
Hi Rick, Thank you for your response. A couple things with the
formula. Sometimes there will be a zero within the cash flow ie (month
1 =$400, month 2 =$0, month 3= $500. The formula will read month 2 as
the last month. Your formula actually inspired me with a new formula.
I did some research through some older models and found old formulas
that I was able to combine. It is SUMPRODUCT((MAX(($Z$2:$EO$20)*($Z
$1:$EO$1)))) Where 2:2 is the cash flow and 1:1 is the date. I can
even expand 2:2 into a table. It seems to be working well-unless I
am missing something. Let me know your thoughts.
Thank you again for your response.