Sumproduct / Max array formula problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andibevan - 24 Aug 2005 09:46 GMT

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

Reply to this Message

Krishnakumar - 24 Aug 2005 10:33 GMT

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

Signature

Krishnakumar

Reply to this Message

Biff - 24 Aug 2005 19:01 GMT

Hi!

Array entered:

=INDEX(Version,MATCH(MAX(IF(File_Name="Test File
1.txt",Date_Val)),Date_Val,0))

Biff

> Hi All,
>
[quoted text clipped - 11 lines]
>
> Andi

Reply to this Message

Harlan Grove - 24 Aug 2005 20:30 GMT

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.

Reply to this Message

Biff - 24 Aug 2005 21:58 GMT

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

> Biff wrote...
>>Array entered:
[quoted text clipped - 29 lines]
> At this point the LOOKUP formula is shorter, and it doesn't need array
> entry.

Reply to this Message

Harlan Grove - 25 Aug 2005 01:18 GMT

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 to this Message