Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Computing the item age
Hi,
Can someone help me with this: I have the following type of data: Itam Tag Last Updated Date AAA 01-Jan-09 AAB 01-Jan-09 AAC 01-Jan-09 AAA 02-Jan-09 AAD 02-Jan-09 AAF 02-Jan-09 BBA 02-Jan-09 AAA 03-Jan-09 AAD 03-Jan-09 BBB 03-Jan-09 AAD 04-Jan-09 Above is the cumulative data for daily reports (report generated on last updated date). An item tag appears in the report it the item is outstanding. And it will keep appearing everyday until it is fixed. I want to compute what is the last day when the appeared in the report, that is, if you look at item AAA, it first appeared on 1-jan-09 and it last appeared on 3-jan-09. So, I should be able to compute the number of days this item was outstanding. I'll appreciate if anyone can help me with this. Thanks. -AG |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Computing the item age
=MAX(IF(A1:A11="AAA",B1:B11,""))-MIN(IF(A1:A11="AAA",B1:B11,""))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200855 "AG" wrote: Hi, Can someone help me with this: I have the following type of data: Itam Tag Last Updated Date AAA 01-Jan-09 AAB 01-Jan-09 AAC 01-Jan-09 AAA 02-Jan-09 AAD 02-Jan-09 AAF 02-Jan-09 BBA 02-Jan-09 AAA 03-Jan-09 AAD 03-Jan-09 BBB 03-Jan-09 AAD 04-Jan-09 Above is the cumulative data for daily reports (report generated on last updated date). An item tag appears in the report it the item is outstanding. And it will keep appearing everyday until it is fixed. I want to compute what is the last day when the appeared in the report, that is, if you look at item AAA, it first appeared on 1-jan-09 and it last appeared on 3-jan-09. So, I should be able to compute the number of days this item was outstanding. I'll appreciate if anyone can help me with this. Thanks. -AG |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Computing the item age
AG wrote:
Hi, Can someone help me with this: I have the following type of data: Itam Tag Last Updated Date AAA 01-Jan-09 AAB 01-Jan-09 AAC 01-Jan-09 AAA 02-Jan-09 AAD 02-Jan-09 AAF 02-Jan-09 BBA 02-Jan-09 AAA 03-Jan-09 AAD 03-Jan-09 BBB 03-Jan-09 AAD 04-Jan-09 Above is the cumulative data for daily reports (report generated on last updated date). An item tag appears in the report it the item is outstanding. And it will keep appearing everyday until it is fixed. I want to compute what is the last day when the appeared in the report, that is, if you look at item AAA, it first appeared on 1-jan-09 and it last appeared on 3-jan-09. So, I should be able to compute the number of days this item was outstanding. I'll appreciate if anyone can help me with this. Thanks. -AG Not sure if this is what you mean--In C2 and fill down, this array formula* will return the max date for the Item Tag. =MAX(IF(A2=$A$2:$A$12,$B$2:$B$12)) *Commit the array formula by pressing Ctrl+Shift+Enter, not just Enter. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Computing the item age
Many thanks both of you. That works for me.
- AG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Computing Time from One Day to Another | Excel Discussion (Misc queries) | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
COUNTIF not computing | Excel Discussion (Misc queries) | |||
COMPUTING TWO COLUMNS | Excel Worksheet Functions | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |