Formula for Last Cell in a Row with a Value

[cardan]

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 cashflow>0,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

 

[Rick Rothstein \(MVP - VB\)]

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]

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









- 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$2>0)*($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.

 

[JMB]

A possible issue could be if the last value in row 3 is duplicated earlier in
that row. Match will return the location of the first instance.

Unless I am misunderstanding something, can it be simplified to:
=LOOKUP(2,1/(3:3<>""),1:1)

Maybe change the <>"" to >0 if that works better for the OP.

 

[Rick Rothstein]

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)

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









- 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$2>0)*($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.

 

[Rick Rothstein]

You are right. See my latest response to the OP for a revised formula.