2

I've searched and searched and trialled every answer but none seem to work in my situation.

Using Excel 2003.

I have a table with the columns [DATE], [SITE], [INCIDENT].

I'll explain in something similar to SQL because it's nice and clear. I need to run the following SELECT statement on the data in that table

SELECT [DATE] from Table1 WHERE [SITE] = "Site1" AND [INCIDENT] = "Death"

From that list (multiple values), I then need to display the latest [DATE] value ie. the last date a death occured.

I've tried this following formula:

{=MAX(IF('Spreadsheet1'!D3:D1000="Death", IF('Spreadsheet1'!E3:E1000="Site1", 'Spreadsheet1'!A3:A1000, 0), 0))}

But this does not seem to work correctly.

Any help is definitely welcome.

  • 1
    Is your sheet definitely named "Spreadsheet1", rather than "Sheet1"? Since this is an array formula, are you entering it with the Ctrl-Shift-Enter key combination? – chuff Aug 2 '13 at 0:23
  • @chuff makes a good point –– I, also, was able to get your formula to work. – Scott Aug 2 '13 at 0:44
  • Works for me too.......Perhaps nitpicking but generically it's better to omit the zeroes, because in other cases if MAX value is a negative number you won't get the right answer, i.e. =MAX(IF(D3:D1000="Death",IF(E3:E1000="Site1",A3:A1000))) - that also gives you a syntax that will work for MIN too...... – barry houdini Aug 2 '13 at 15:06
2

Try

=MAX(A3:A1000 * (D3:D1000="Death") * (E3:E1000="Site1"))

Rather than treating your WHERE clauses as conditions, this simply treats them as part of the expression that you are maximizing.  TRUE is 1 and FALSE is 0, so value × boolean1 × boolean2 is value if both Booleans are TRUE and 0 otherwise.

Naturally, it’s an array formula; but I see you already understand those.

| improve this answer | |
  • Shouldn't this formula have the curly brackets around it Ctrl Shift Enter – wbeard52 Aug 2 '13 at 3:37
  • 1
    @wbeard52: It’s a question of convention. Yes, if the formula is properly entered with (Ctrl)+(Shift)+(Enter), then it will be displayed with curly braces. But I showed what a user would need to type before pressing the (Ctrl)+(Shift)+(Enter). – Scott Aug 2 '13 at 19:13
1

Make sure that your sheet name matches the one used in the formula (I assume it does) and that you are entering the formula with the Control-Shift-Enter key combination, since it is an array formula. The formula worked fine for me.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.