1) Data is contained in a workbook called Template New 2006
F4:BE4 contains date references; breaking the full year down into
weeks.
F31:BE31 contains £ returns from customers;some cells are left blank.

New Workbook is being created to track these returns against the date
of the return.

Objective: How can I get Excel to return the average £ of the five most
recent returns, ignoring any blank cells?

2) One workbook; two worksheets.
sheet 2 contains 3 columns; column a = id #, column b = date, column c
= any comments on the customer. Note: there are usually several notes
per customer.

In sheet 1 I would like to show the most *recent* comment in column c
for a specified id # (in this case example 004)

Can anyone help?


--
Aoife
------------------------------------------------------------------------
Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
View this thread: http://www.excelforum.com/showthread...hreadid=531306

There are two answers to your first question. If your data is:

9-Apr-06 2
8-Apr-06 4
7-Apr-06
6-Apr-06
5-Apr-06
4-Apr-06 100
3-Apr-06 200
2-Apr-06 4

The first is to take the five most recent:

9-Apr-06 2
8-Apr-06 4
7-Apr-06
6-Apr-06
5-Apr-06

and average the non-blanks to get 3

The second approach is to get the most recent non-blanks:

9-Apr-06 2
8-Apr-06 4
4-Apr-06 100
3-Apr-06 200
2-Apr-06 4

and average to get 62.


Which do you want??


--
Gary''s Student


"Aoife" wrote:


1) Data is contained in a workbook called Template New 2006
F4:BE4 contains date references; breaking the full year down into
weeks.
F31:BE31 contains £ returns from customers;some cells are left blank.

New Workbook is being created to track these returns against the date
of the return.

Objective: How can I get Excel to return the average £ of the five most
recent returns, ignoring any blank cells?

2) One workbook; two worksheets.
sheet 2 contains 3 columns; column a = id #, column b = date, column c
= any comments on the customer. Note: there are usually several notes
per customer.

In sheet 1 I would like to show the most *recent* comment in column c
for a specified id # (in this case example 004)

Can anyone help?


--
Aoife
------------------------------------------------------------------------
Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
View this thread: http://www.excelforum.com/showthread...hreadid=531306

Your first question looks identical to the question you asked under the
heading 'formula help' which was answered by Bob Phillips, and not
acknowledged by you.

If you get no better answer to your second question, you could sort
your comments worksheet into reverse date order (latest date first, as
a third sheet if required) and use a simple VLookup with the False
option, this will select only the first (latest) encountered item.

--

Gary''s Student Wrote:
There are two answers to your first question. If your data is:

9-Apr-06 2
8-Apr-06 4
7-Apr-06
6-Apr-06
5-Apr-06
4-Apr-06 100
3-Apr-06 200
2-Apr-06 4

The first is to take the five most recent:

9-Apr-06 2
8-Apr-06 4
7-Apr-06
6-Apr-06
5-Apr-06

and average the non-blanks to get 3

The second approach is to get the most recent non-blanks:

9-Apr-06 2
8-Apr-06 4
4-Apr-06 100
3-Apr-06 200
2-Apr-06 4

and average to get 62.


Which do you want??


--
Gary''s Student


"Aoife" wrote:


1) Data is contained in a workbook called Template New 2006
F4:BE4 contains date references; breaking the full year down into
weeks.
F31:BE31 contains £ returns from customers;some cells are left
blank.

New Workbook is being created to track these returns against the
date
of the return.

Objective: How can I get Excel to return the average £ of the five
most
recent returns, ignoring any blank cells?

2) One workbook; two worksheets.
sheet 2 contains 3 columns; column a = id #, column b = date, column
c
= any comments on the customer. Note: there are usually several
notes
per customer.

In sheet 1 I would like to show the most *recent* comment in column
c
for a specified id # (in this case example 004)

Can anyone help?


--
Aoife

------------------------------------------------------------------------
Aoife's Profile:
http://www.excelforum.com/member.php...o&userid=33191
View this thread:
http://www.excelforum.com/showthread...hreadid=531306




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531306