|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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,
|
|
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
"Vital Miranda" <VitalMiranda[ at ]discussions.microsoft.com> wrote in message
news:D52D486A-06D9-4083-AB5B-7EFC948DF0D3[ at ]microsoft.com...
[Quoted Text] > 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,
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?
"Biff" wrote:
[Quoted Text] > 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
>
> "Vital Miranda" <VitalMiranda[ at ]discussions.microsoft.com> wrote in message
> news:D52D486A-06D9-4083-AB5B-7EFC948DF0D3[ at ]microsoft.com...
> > 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,
>
>
>
|
|
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
Vital Miranda wrote:
[Quoted Text] > 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?
>
> "Biff" wrote:
>
> > 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
> >
> > "Vital Miranda" <VitalMiranda[ at ]discussions.microsoft.com> wrote in message
> > news:D52D486A-06D9-4083-AB5B-7EFC948DF0D3[ at ]microsoft.com...
> > > 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,
> >
> >
> >
|
|
That was really fast! Thanks a lot Pete. The formula worked great.
Again, Thanks
Vital
"Pete_UK" wrote:
[Quoted Text] > 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
>
> Vital Miranda wrote:
> > 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?
> >
> > "Biff" wrote:
> >
> > > 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
> > >
> > > "Vital Miranda" <VitalMiranda[ at ]discussions.microsoft.com> wrote in message
> > > news:D52D486A-06D9-4083-AB5B-7EFC948DF0D3[ at ]microsoft.com...
> > > > 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,
> > >
> > >
> > >
>
>
|
|
You're welcome.
Pete
Vital Miranda wrote:
[Quoted Text] > That was really fast! Thanks a lot Pete. The formula worked great.
>
> Again, Thanks
>
> Vital
>
> "Pete_UK" wrote:
>
> > 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
> >
> > Vital Miranda wrote:
> > > 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?
> > >
> > > "Biff" wrote:
> > >
> > > > 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
> > > >
> > > > "Vital Miranda" <VitalMiranda[ at ]discussions.microsoft.com> wrote in message
> > > > news:D52D486A-06D9-4083-AB5B-7EFC948DF0D3[ at ]microsoft.com...
> > > > > 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,
> > > >
> > > >
> > > >
> >
> >
|
|
|