Formula for Last Cell in a Row with a Value

  • Thread starter Thread starter cardan
  • Start date Start date
C

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
 
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
 
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 -
Click to expand...

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.
 
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.
 
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 -
Click to expand...

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