Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dmax formula
I have a spreadsheet with data from a1..j266 listing all of our injuries by
Rig no. and dates, etc. I want to pull the latest injury date per rig. I have a define name of "database" (which covers a1..j266) Defiine name of "criteria" (which covers L1..M2) Column 6 is "Date of Accident" On L1 - typed Rig No. On M1 - typed Last recordable date On M4 - typed =dmax(database,6,criteria) L5-L45 - typed rig nos. ***** When I was using Excel 97, I would then go to Data, Table, in the Column Input Cell box, select L2, OK and formula would work. We have updated to Excel 2007, I get all the way to the ******; then can't find the same options of Data, Table, Column Input Cell Box for the formula to work. Can someone please tell me what I need to do? Thanks, Angie L |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dmax formula
Hi,
in Excel 2007, Data tables are located under Data Data Tools What if analysis Data Table -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Angie G." wrote in message ... I have a spreadsheet with data from a1..j266 listing all of our injuries by Rig no. and dates, etc. I want to pull the latest injury date per rig. I have a define name of "database" (which covers a1..j266) Defiine name of "criteria" (which covers L1..M2) Column 6 is "Date of Accident" On L1 - typed Rig No. On M1 - typed Last recordable date On M4 - typed =dmax(database,6,criteria) L5-L45 - typed rig nos. ***** When I was using Excel 97, I would then go to Data, Table, in the Column Input Cell box, select L2, OK and formula would work. We have updated to Excel 2007, I get all the way to the ******; then can't find the same options of Data, Table, Column Input Cell Box for the formula to work. Can someone please tell me what I need to do? Thanks, Angie L |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dmax formula
How about a much easier to understand and implement alternative?
I assume the latest injury date means the *most recent date*. A2:An = rig numbers B2:Bn = dates of injury D2:Dn = unique rig numbers Enter this array formua** in E2 to return the most recent date for each rig: =MAX(IF(A$2:A$n=D2,B$2:B$n)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed and format as Date. -- Biff Microsoft Excel MVP "Angie G." wrote in message ... I have a spreadsheet with data from a1..j266 listing all of our injuries by Rig no. and dates, etc. I want to pull the latest injury date per rig. I have a define name of "database" (which covers a1..j266) Defiine name of "criteria" (which covers L1..M2) Column 6 is "Date of Accident" On L1 - typed Rig No. On M1 - typed Last recordable date On M4 - typed =dmax(database,6,criteria) L5-L45 - typed rig nos. ***** When I was using Excel 97, I would then go to Data, Table, in the Column Input Cell box, select L2, OK and formula would work. We have updated to Excel 2007, I get all the way to the ******; then can't find the same options of Data, Table, Column Input Cell Box for the formula to work. Can someone please tell me what I need to do? Thanks, Angie L |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max() Dmax() Looking for MaxIf() Equiv. | Excel Worksheet Functions | |||
DMAX taken to the max | Excel Worksheet Functions | |||
How do I DMAX only part of a column? | Excel Worksheet Functions | |||
DMAX wrong calculation | Excel Worksheet Functions | |||
DMAX criteria | Excel Worksheet Functions |