Formula for Last Cell in a Row with a Value
 

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

Formula for Last Cell in a Row with a Value
 

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

Formula for Last Cell in a Row with a Value
 

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.

Formula for Last Cell in a Row with a Value
 

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 (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

Formula for Last Cell in a Row with a Value
 

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.

Formula for Last Cell in a Row with a Value
 

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

--
Rick (MVP - Excel)


"JMB" wrote in message
...
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 (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