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.

Improve this question
3
  • 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, 2013 at 0:23
  • @chuff makes a good point –– I, also, was able to get your formula to work.
    – Scott - Слава Україні
    Aug 2, 2013 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, 2013 at 15:06

2 Answers 2

Reset to default
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
2
  • Shouldn't this formula have the curly brackets around it Ctrl Shift Enter
    – wbeard52
    Aug 2, 2013 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, 2013 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .