I have a table of data where the top row contains this month's dates, and the
cell beneath each date contains a bank balance. Can you please help me
create a formula to put in the far right column(directly after the last date
of the month) that will return the value of the second to last bank balance I
have entered.

For example, today (7/24) I entered a balance, so I want the formula to
automatically update with the bank balance of 7/23.

Thanks in advance!

Assuming all the cells up to today's date contain an entry:

B1:AF1 = dates
B2:AF2 = balance

=INDEX(B2:AF2,MATCH(TODAY(),B1:AF1,0)-1)

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
I have a table of data where the top row contains this month's dates, and
the
cell beneath each date contains a bank balance. Can you please help me
create a formula to put in the far right column(directly after the last
date
of the month) that will return the value of the second to last bank
balance I
have entered.

For example, today (7/24) I entered a balance, so I want the formula to
automatically update with the bank balance of 7/23.

Thanks in advance!

Maybe

=INDEX(2:2,LARGE(IF((A2:N2<"")*(A2:N2<""),COLUMN (A2:N2)),2))


Returns the second to last value in A2 - N2
Which is an array so submit with Ctrl+Shift+Enter

Mike

"Bob" wrote:

I have a table of data where the top row contains this month's dates, and the
cell beneath each date contains a bank balance. Can you please help me
create a formula to put in the far right column(directly after the last date
of the month) that will return the value of the second to last bank balance I
have entered.

For example, today (7/24) I entered a balance, so I want the formula to
automatically update with the bank balance of 7/23.

Thanks in advance!

Hmmm...

I just thought of something...

If today is the 1st of the month, then what?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming all the cells up to today's date contain an entry:

B1:AF1 = dates
B2:AF2 = balance

=INDEX(B2:AF2,MATCH(TODAY(),B1:AF1,0)-1)

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
I have a table of data where the top row contains this month's dates, and
the
cell beneath each date contains a bank balance. Can you please help me
create a formula to put in the far right column(directly after the last
date
of the month) that will return the value of the second to last bank
balance I
have entered.

For example, today (7/24) I entered a balance, so I want the formula to
automatically update with the bank balance of 7/23.

Thanks in advance!