MAX Formula with Condition

[shemayisroel]

Hi,

Below is a sample data table, what I'm looking to do is apply a MAX formula but have a maximum based off the state.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>State 1</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>State 2</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>State 3</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>State 2</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>State 3</TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>State 1</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>State 2</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>State 3</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>State 1</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>State 1</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>State 1</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>State 2</TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>State 3</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>State 2</TD><TD style="TEXT-ALIGN: right">8</TD></TR></TBODY></TABLE>

 

[hkaplan2]

You could do a pivot table, putting the state field in the row area and the amount field in the data area and setting the amount field to display max.

That will give you this:

State 1 10
State 2 15
State 3 15

Does that give you what you want?

 

[Richard Schollar]

Perhaps an array formula such as:

=MAX(IF(A1:A15="State 1",B1:B15))

Confirmed with Ctrl+Shif+Enter.

 

[shemayisroel]

You could do a pivot table, putting the state field in the row area and the amount field in the data area and setting the amount field to display max.

That will give you this:

State 1 10
State 2 15
State 3 15

Does that give you what you want?

Thanks however I was looking for a formula solution

 

[shemayisroel]

Perhaps an array formula such as:

=MAX(IF(A1:A15="State 1",B1:B15))

Confirmed with Ctrl+Shif+Enter.

Yes exactly what I was looking for - thank you so much.

I also found the answer here

http://www.ozgrid.com/forum/showthread.php?t=66721