Conditional MAX Function

Keithcornett · Oct 22nd 2007

I have a file that has multiple account numbers. I'm trying to get the largest value of the code column (see attached) to align with the account number that has the patient count of "1". Is there a way to use the Max function to do this? Thanks for any help on this.

turtle44 · Oct 22nd 2007

Re: Max Function By Account Number

Try this array formula (confirmed with CTRL+Shift+Enter) in N2 and copied down.
[bfn]=IF(L2=1,MAX(($A$2:$A$23=A2)*$K$2:$K$23),0)[/bfn]

daddylonglegs · Oct 22nd 2007

Re: Max Function By Account Number

Hello Keith,

Try this formula in N2 copied down

=IF(L2=1,MAX(IF(A2:A$23=A2,K2:K$23)),"")

It's an array formula and therefore needs to be confirmed with CTRL+SHIFT+ENTER. To do that select cell with formula and press F2. Then hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar

Note: You probably don't need column L, this would do the same for your example:

=IF(A2<>A1,MAX(IF(A2:A$23=A2,K2:K$23)),"")

also confirmed with CTRL+SHIFT+ENTER

Keithcornett · Oct 22nd 2007

Re: Max Function By Account Number

That works perfectly! Thank you so much for the help...

Conditional MAX Function

Participate now!

Share