Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumproduct / Max array formula problem
Hi All,
I have a spreadsheet with 3 named ranges:- File_Name (name of file) Version (file version) Date_Val (date when version was saved). For a given file (Test File1.txt) how do I return the version number of the most recently saved document? Thanks in advance Andi |
#2
|
|||
|
|||
Hi Andi, If you sort the data in ascending order with the key Date_Val, try this, =LOOKUP(2,1/(A2:A30=D1),B2:B30) where D1 houses the file name HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=398515 |
#3
|
|||
|
|||
Hi!
Array entered: =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)),Date_Val,0)) Biff "Andibevan" wrote in message ... Hi All, I have a spreadsheet with 3 named ranges:- File_Name (name of file) Version (file version) Date_Val (date when version was saved). For a given file (Test File1.txt) how do I return the version number of the most recently saved document? Thanks in advance Andi |
#4
|
|||
|
|||
Biff wrote...
Array entered: =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)), Date_Val,0)) .... LOOKUP wouldn't need array entry. =LOOKUP(2,1/(File_Name="Test File 1.txt") /(Date_Val=MAX(((File_Name="Test File 1.txt"))*Date_Val)),Version) Also, you've got a bug in your formula. If any files appeared before 'Test File 1.txt' and some versions of those preceding files had the same date as the latest version of 'Test File 1.txt', then your formula would return the version from the topmost other file with that date. For example, FileName Version Date foobar 1 8/22/2005 foobar 2 8/23/2005 foobar 3 8/24/2005 Test File 1.txt 1 8/17/2005 Test File 1.txt 2 8/24/2005 Your formula would return 3, not 2. In order to avoid that problem, your INDEX formula would need to be revised as =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)), IF(File_Name="Test File 1.txt",Date_Val),0)) At this point the LOOKUP formula is shorter, and it doesn't need array entry. |
#5
|
|||
|
|||
Good catch!
Easily fixed using either your modified suggestion or: =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)),(File_Name="Test File 1.txt")*Date_Val,0)) At this point the LOOKUP formula is shorter, and it doesn't need array entry. Another good point! I'm not comfortable using that Lookup technique just yet. I don't fully understand how to test for multiple criteria. One of these days! Biff "Harlan Grove" wrote in message oups.com... Biff wrote... Array entered: =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)), Date_Val,0)) ... LOOKUP wouldn't need array entry. =LOOKUP(2,1/(File_Name="Test File 1.txt") /(Date_Val=MAX(((File_Name="Test File 1.txt"))*Date_Val)),Version) Also, you've got a bug in your formula. If any files appeared before 'Test File 1.txt' and some versions of those preceding files had the same date as the latest version of 'Test File 1.txt', then your formula would return the version from the topmost other file with that date. For example, FileName Version Date foobar 1 8/22/2005 foobar 2 8/23/2005 foobar 3 8/24/2005 Test File 1.txt 1 8/17/2005 Test File 1.txt 2 8/24/2005 Your formula would return 3, not 2. In order to avoid that problem, your INDEX formula would need to be revised as =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)), IF(File_Name="Test File 1.txt",Date_Val),0)) At this point the LOOKUP formula is shorter, and it doesn't need array entry. |
#6
|
|||
|
|||
Biff wrote...
.... Easily fixed using either your modified suggestion or: =INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt", Date_Val)),(File_Name="Test File 1.txt")*Date_Val,0)) which you could reduce further to =INDEX(Version,MATCH(MAX((File_Name="Test File 1.txt")*Date_Val), (File_Name="Test File 1.txt")*Date_Val,0)) I'm not comfortable using that Lookup technique just yet. I don't fully understand how to test for multiple criteria. If the multiple criteria would always result in a single entry satisfying the criteria or all matches would be equally acceptable, LOOKUP(2,1/((CriteriaExpression1)*(criteriaExpression2)*...), ReturnValues) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula w/ Multiple SumIf Criteria | Excel Worksheet Functions | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
Propagate Array Formula Down Column | Excel Discussion (Misc queries) | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions | |||
Help with array formula | Excel Worksheet Functions |