# Conditional MAX Function

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• 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.

## Files

• 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]

• 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

• Re: Max Function By Account Number

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

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!