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,