Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default "MAXIF" Equivalent function in Excel

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,
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default "MAXIF" Equivalent function in Excel

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" wrote in message
...
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,



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default "MAXIF" Equivalent function in Excel

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" wrote in message
...
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,




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default "MAXIF" Equivalent function in Excel

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" wrote in message
...
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,





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default "MAXIF" Equivalent function in Excel

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" wrote in message
...
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,







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default "MAXIF" Equivalent function in Excel

You're welcome.

Pete

Vital Miranda wrote:
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" wrote in message
...
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,






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IS THERE AN INTERPOLATION FUNCTION IN EXCEL 2003 Cooper Excel Worksheet Functions 2 December 23rd 05 05:51 AM
How do i execute a VBA function by clicking on an excel cell? Matthew Excel Discussion (Misc queries) 1 December 7th 05 02:10 AM
Excel 2003 Slow Function Argument Window [email protected] Excel Discussion (Misc queries) 2 June 28th 05 06:53 PM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM


All times are GMT +1. The time now is 02:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"