I have a log, containing 4 columns. First 2 columns contain dept name and
team name respectively. Third column contains a timestamp at which the row
was added to the spreadsheet. By design, we always add a row at the end of
the existing list (thus an append, never an insert between existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination (via 2 cells), I
would like to have the value of the 4th column returned for the
chronologically last entry in the list for the dept + team combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed that records are
always added in chronological order and thus the last record with the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination (via 2
cells), I
would like to have the value of the 4th column returned for the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed that
records are
always added in chronological order and thus the last record with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

Wow, incredible. I did not understand the logic behind the formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination (via 2
cells), I
would like to have the value of the 4th column returned for the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed that
records are
always added in chronological order and thus the last record with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that €œThe
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

Hi there,

I am using this formula to find the last entry with today's date in an array
in a bank statement tracking sheet, which shows the current balance of the
account based on today's date. This works perfectly when today's date is in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance prior to
today's date if no transactions have been recorded for today - e.g. the last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that €œThe
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

Typically, the current balance is the last entry in the balance column so it
shouldn't matter what the date is. Just return the *last* entry from the
column:

Column F is the balance column

=LOOKUP(1E100,F:F)

--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi there,

I am using this formula to find the last entry with today's date in an
array
in a bank statement tracking sheet, which shows the current balance of the
account based on today's date. This works perfectly when today's date is
in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance prior
to
today's date if no transactions have been recorded for today - e.g. the
last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that "The
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

Hi, thanks for your relpy.

I show uncleared balances as well so there may be other entries below the
last cleared entry.

I need it to calculate the last cleared balance entry and ignore anything
later than today's date.

Any ideas?

Thanks



"T. Valko" wrote:

Typically, the current balance is the last entry in the balance column so it
shouldn't matter what the date is. Just return the *last* entry from the
column:

Column F is the balance column

=LOOKUP(1E100,F:F)

--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi there,

I am using this formula to find the last entry with today's date in an
array
in a bank statement tracking sheet, which shows the current balance of the
account based on today's date. This works perfectly when today's date is
in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance prior
to
today's date if no transactions have been recorded for today - e.g. the
last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that "The
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

I need it to calculate the last cleared balance entry and
ignore anything later than today's date.

Ok, so how do you denote "cleared" ?

In my register I use a simple "X".

So, all you should have to do if find the *last* X (or whatever method you
use to denote cleared) and the corresponding balance.

You can modify the original formula:

=LOOKUP(2,1/(C1:C6="x"), D1:D6)

Where column C is the cleared column and column D is the balance column.

If this still doesn't work then you'll have to post an example so I can see
exactly what you mean.



--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi, thanks for your relpy.

I show uncleared balances as well so there may be other entries below the
last cleared entry.

I need it to calculate the last cleared balance entry and ignore anything
later than today's date.

Any ideas?

Thanks



"T. Valko" wrote:

Typically, the current balance is the last entry in the balance column so
it
shouldn't matter what the date is. Just return the *last* entry from the
column:

Column F is the balance column

=LOOKUP(1E100,F:F)

--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi there,

I am using this formula to find the last entry with today's date in an
array
in a bank statement tracking sheet, which shows the current balance of
the
account based on today's date. This works perfectly when today's date
is
in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance
prior
to
today's date if no transactions have been recorded for today - e.g. the
last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that "The
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

I use the sheet to input future known transactions - some of them have known
dates (e.g. rent) and some are card transactions with a known transaction
date (but this is obviously a few days earlier than the date it clears the
bank). I don't actually mark them "cleared", although I could.

I've decided to cheat and use a VLOOKUP to find the nearest date to today's
date and hide this in another cell.

Thanks for your help anyway!

"T. Valko" wrote:

I need it to calculate the last cleared balance entry and
ignore anything later than today's date.

Ok, so how do you denote "cleared" ?

In my register I use a simple "X".

So, all you should have to do if find the *last* X (or whatever method you
use to denote cleared) and the corresponding balance.

You can modify the original formula:

=LOOKUP(2,1/(C1:C6="x"), D1:D6)

Where column C is the cleared column and column D is the balance column.

If this still doesn't work then you'll have to post an example so I can see
exactly what you mean.



--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi, thanks for your relpy.

I show uncleared balances as well so there may be other entries below the
last cleared entry.

I need it to calculate the last cleared balance entry and ignore anything
later than today's date.

Any ideas?

Thanks



"T. Valko" wrote:

Typically, the current balance is the last entry in the balance column so
it
shouldn't matter what the date is. Just return the *last* entry from the
column:

Column F is the balance column

=LOOKUP(1E100,F:F)

--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi there,

I am using this formula to find the last entry with today's date in an
array
in a bank statement tracking sheet, which shows the current balance of
the
account based on today's date. This works perfectly when today's date
is
in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance
prior
to
today's date if no transactions have been recorded for today - e.g. the
last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that "The
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

Ok, glad you got it worked out.

--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
I use the sheet to input future known transactions - some of them have
known
dates (e.g. rent) and some are card transactions with a known transaction
date (but this is obviously a few days earlier than the date it clears the
bank). I don't actually mark them "cleared", although I could.

I've decided to cheat and use a VLOOKUP to find the nearest date to
today's
date and hide this in another cell.

Thanks for your help anyway!

"T. Valko" wrote:

I need it to calculate the last cleared balance entry and
ignore anything later than today's date.

Ok, so how do you denote "cleared" ?

In my register I use a simple "X".

So, all you should have to do if find the *last* X (or whatever method
you
use to denote cleared) and the corresponding balance.

You can modify the original formula:

=LOOKUP(2,1/(C1:C6="x"), D1:D6)

Where column C is the cleared column and column D is the balance column.

If this still doesn't work then you'll have to post an example so I can
see
exactly what you mean.



--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi, thanks for your relpy.

I show uncleared balances as well so there may be other entries below
the
last cleared entry.

I need it to calculate the last cleared balance entry and ignore
anything
later than today's date.

Any ideas?

Thanks



"T. Valko" wrote:

Typically, the current balance is the last entry in the balance column
so
it
shouldn't matter what the date is. Just return the *last* entry from
the
column:

Column F is the balance column

=LOOKUP(1E100,F:F)

--
Biff
Microsoft Excel MVP


"tigger" wrote in message
...
Hi there,

I am using this formula to find the last entry with today's date in
an
array
in a bank statement tracking sheet, which shows the current balance
of
the
account based on today's date. This works perfectly when today's
date
is
in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance
prior
to
today's date if no transactions have been recorded for today - e.g.
the
last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

"Hans Knudsen" wrote:

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micro...66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D1:D6)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that "The
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans











"DKS" skrev i en meddelelse
...
Wow, incredible. I did not understand the logic behind the
formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to
know the
logic behind your formula.

"Hans Knudsen" wrote:

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1:D100)
Where F1 holds department and team name combination.

Regards
Hans


"DKS" skrev i en meddelelse
...
I have a log, containing 4 columns. First 2 columns contain
dept
name and
team name respectively. Third column contains a timestamp at
which the row
was added to the spreadsheet. By design, we always add a row
at
the end of
the existing list (thus an append, never an insert between
existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination
(via 2
cells), I
would like to have the value of the 4th column returned for
the
chronologically last entry in the list for the dept + team
combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed
that
records are
always added in chronological order and thus the last record
with
the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.