Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Problems
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Problems
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Problems
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
elapsed time problems | Excel Worksheet Functions | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
Pivot table problems | Excel Discussion (Misc queries) | |||
Integrity Problems in PivotTables | Excel Discussion (Misc queries) | |||
I'm getting mad - I'm having problems with EXCEL file name length | Excel Discussion (Misc queries) |