"MAXIF" Equivalent function in Excel

[Guest]

At work, base on several parameters, we assign risks (1=Low Risk, 2=Medium
Risk and 3=High Risk) to each account. Customers may have 1 or more accounts.
We have decided that the risk to assign to each customer will be the same
risk of the account having the highest risk. That is, if a customer has 3
accounts (2 of them are rated 1 and one is rated 3), we will consider the
customer having a risk of 3.

On a monthly basis I get a spreadsheet of about 40,000 accounts belongin to
about 18,000 customers. Using a formula I calculate the "Account Risk" for
all the 40,000 accounts.


To calculate the "Customer Risk", I have been sorting by Customer in
ascending order and Account Risk in descending order, and using the formula
IF(A2=A1,D1,C2). However, because the file is shared by other users, they are
constantly sorting by other columns and updating fields of information that
may change the risk at the account level. Therefore, to update the risk at
the customer level I have to do the sorting and formula explained above on a
daily basis.

If Excel had a "MAXIF" or "MINIF" function, I could use it to update the
Customer Risk without the need of doing the sorting. Ie.: In cell D2 (Cust
Risk):
MAXIF($A:$A,A2) and copy it to the rest of the cells.

Since Excel does not have this function, what formula can use to calculate
the Customer Risk (See the example below)

A B C D
1 Cust # Acct. # Acct Risk Cust Risk
2 100 12345 1
3 125 23456 2
4 130 13571 2
5 135 58731 1
6 140 35771 1
7 100 12346 2
8 115 98765 3
9 112 67672 1
10 100 12347 3
11 135 69331 3
12 112 79871 2
13 140 53332 3
14 130 13572 1
15 115 98764 2
16 125 23457 2
17 140 37939 2
18 130 13573 3

Any help would be greatly appreciated.

Thanks,

 

[Biff]

Hi!

There is a MAX IF....

=MAX(IF(A$2:A$18=A2,C$2:C$18))

Which is an array formula. Enter it using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

Here's another way that isn't an array (normally entered):

=SUMPRODUCT(MAX((A$2:A$18=A2)*C$2:C$18))

Biff

 

[Biff]

Hi!

There is a MAX IF....

=MAX(IF(A$2:A$18=A2,C$2:C$18))

Which is an array formula. Enter it using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

Here's another way that isn't an array (normally entered):

=SUMPRODUCT(MAX((A$2:A$18=A2)*C$2:C$18))

Biff

 

[Guest]

Biff,

Thanks a million for the response. The formula worked great!

I tried to use the same formula, but using "MIN" instead of "MAX" to get the
equivalent of "MINIF". However, I get zeros (0) only.

Is there an equivalent to "MINIF"?

Thanks a lot for your help?

 

[Pete_UK]

Try this:

=MIN(IF(A$2:A$18=A2,C$2:C$18,9999999999))

committed with CTRL-SHIFT-ENTER as before. If A2 is not equal to one of
the cells in the range, then the other formula would have taken 0 as
the value, as you did not specify the alternative - here, I've made the
alternative a very large number, so one of the cells in the range ought
to be smaller than this.

Hope this helps.

Pete

 

[Guest]

That was really fast! Thanks a lot Pete. The formula worked great.

Again, Thanks

Vital

 

[Pete_UK]

You're welcome.

Pete

That was really fast! Thanks a lot Pete. The formula worked great.

Again, Thanks

Vital