Two Problems

[Aoife]

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?

[Gary''s Student]

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]

Your first question looks identical to the question you asked under the heading 'formula help' which was answered by Bob Phillips, and not yet 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.

--

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