Array Formula: Max & Large, with criteria

[Sephaerius]

I've got a spreadsheet where I need to identify three things: for each unique account number, find the max date, then within that, find the largest dollar amount.
So I've got three columns I'm working with, Account Number, Date, and Amount. The Account Number column may have 10,000 records, but only 200 or so are unique. Within that, each repeated account number has a Date, some of those repeated as well, but I only need to find the MAX, then within that, the Large of the Amount column.

I'd like to return TRUE/FALSE, so when I filter on TRUE, the results will be each unique account number's max date with associated largest amount. I hope that makes sense.

I'm nearly there with a non-array formula =IF(AND(MAX(B:B)=B2,LARGE(C:C,1)=C2),TRUE,FALSE) where B is the Date and C is the Amount. But this returns only the 1 row within the entire document, whereas I need to identify that row for each Account Number, so I'm thinking this will be an array instead.

I've been playing with =MAX(IF(... but can't quite seem to get there with it.

Thanks in advance,

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(IF(AccountNumber=Account,IF(Date=MAX(IF(AccountNumber=Account,Date),Amount)))

 

[Sephaerius]

Control+shift+enter, not just enter:

=MAX(IF(AccountNumber=Account,IF(Date=MAX(IF(AccountNumber=Account,Date),Amount)))

Thanks, but Excel won't take that formula. Where am I going wrong in my interpretation?

{=MAX(IF(A:A=A2,IF(B:B=MAX(IF(A:A=A2,B2),C2))))}

 

[Tetra201]

Try the following array formula. It has fewer function calls and the correct number of opening and closing brackets:

=MAX(IF(($B$2:$B$20000=MAX(IF($A$2:$A$20000=$A2,$B$2:$B$20000)))*($A$2:$A$20000=$A2),$C$2:$C$20000))

 

[Sephaerius]

Try the following array formula. It has fewer function calls and the correct number of opening and closing brackets:

=MAX(IF(($B$2:$B$20000=MAX(IF($A$2:$A$20000=$A2,$B$2:$B$20000)))*($A$2:$A$20000=$A2),$C$2:$C$20000))

Thank you, but this isn't exactly what I'm aiming for. This function returns the value from the Amount column, repeated in every cell. It does do this for each unique account number, but that doesn't allow me to filter down to only the rows I'm looking for. I'm hoping to return a TRUE value on the actual line that meets the criteria of being the largest amount, within the max date, for each account number.

It might look something like this:

Account Number Date Amount Array Formula
B1234 5/1/17 $50 FALSE
B1244 7/5/17 $10 FALSE
B1234 7/5/17 $50 TRUE
B9876 5/1/17 $90 FALSE
B9876 7/9/17 $80 TRUE
B9876 7/5/17 $80 FALSE

 

[Aladin Akyurek]

Thanks, but Excel won't take that formula. Where am I going wrong in my interpretation?

{=MAX(IF(A:A=A2,IF(B:B=MAX(IF(A:A=A2,B2),C2))))}

Let E2 house an account number of interest; A2:A400 account numbers, B2:B400 dates, and C2:C400 amounts.

Control+shift+enter:

=MAX(IF(A2:A400=E2,IF(B2:B400=MAX(IF(A2:A400=E2,B2:B400)),C2:C400)))

 

[Marcelo Branco]

Thank you, but this isn't exactly what I'm aiming for. This function returns the value from the Amount column, repeated in every cell. It does do this for each unique account number, but that doesn't allow me to filter down to only the rows I'm looking for. I'm hoping to return a TRUE value on the actual line that meets the criteria of being the largest amount, within the max date, for each account number.

It might look something like this:

Account Number Date Amount Array Formula
B1234 5/1/17 $50 FALSE
B1244 7/5/17 $10 FALSE
B1234 7/5/17 $50 TRUE
B9876 5/1/17 $90 FALSE
B9876 7/9/17 $80 TRUE
B9876 7/5/17 $80 FALSE

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Account​

[/TD]
[TD]

Date​

[/TD]
[TD]

Amount​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

B1234​

[/TD]
[TD]

05/01/2017​

[/TD]
[TD]

70​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B1234​

[/TD]
[TD]

07/05/2017​

[/TD]
[TD]

10​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B1234​

[/TD]
[TD]

07/05/2017​

[/TD]
[TD]

50​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

B9876​

[/TD]
[TD]

05/01/2017​

[/TD]
[TD]

90​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

B9876​

[/TD]
[TD]

07/09/2017​

[/TD]
[TD]

80​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

B9876​

[/TD]
[TD]

07/05/2017​

[/TD]
[TD]

80​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D2 copied down
=AND(B2=MAX(IF(A$2:A$7=A2,B$2:B$7)),C2=MAX(IF(A$2:A$7=A2,IF(B$2:B$7=MAX(IF(A$2:A$7=A2,B$2:B$7)),C$2:C$7))))
Ctrl+Shift+Enter

Remark: for the sake of performance avoid references to entire columns in array formulas.

M.

 

[Sephaerius]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Account​

[/TD]
[TD]

Date​

[/TD]
[TD]

Amount​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

B1234​

[/TD]
[TD]

05/01/2017​

[/TD]
[TD]

70​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B1234​

[/TD]
[TD]

07/05/2017​

[/TD]
[TD]

10​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B1234​

[/TD]
[TD]

07/05/2017​

[/TD]
[TD]

50​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

B9876​

[/TD]
[TD]

05/01/2017​

[/TD]
[TD]

90​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

B9876​

[/TD]
[TD]

07/09/2017​

[/TD]
[TD]

80​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

B9876​

[/TD]
[TD]

07/05/2017​

[/TD]
[TD]

80​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D2 copied down
=AND(B2=MAX(IF(A$2:A$7=A2,B$2:B$7)),C2=MAX(IF(A$2:A$7=A2,IF(B$2:B$7=MAX(IF(A$2:A$7=A2,B$2:B$7)),C$2:C$7))))
Ctrl+Shift+Enter

Remark: for the sake of performance avoid references to entire columns in array formulas.

M.

This is great! Works exactly like I was looking for, thank you! I had the entire column selected because my end-user won't know how many records each spreadsheet contains. This formula will be applied to a regularly generated document in order to reconcile accounts. There may be 10,000, or 100,000, etc. Is it possible to modify this formula to dynamically adjust for that? The alternative would be to just give it enough buffer and hope that the number of records never exceeds it.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.
If the expected (probable) maximum number of rows is 100K, use A2:A100000, never A:A
Alternatives:
- a Table
- dynamic named ranges - try searching in Mr. Excel Forum on how to create them.

M.