IF and MAX statements
 

I am trying to set up a formula that finds the maximum value, the
second-highest value, third-highest value, etc. in a column, depending on
whether the values in another column meet a certain criteria.

In Column C, I want to return the highest value in Column A if the value in
Column B is 48 or higher. Then the second-highest value, third-highest, etc.

Example:
Column A Column B
..100 52
..200 14
..300 32
..400 123
..500 101

Can anyone help?

Thanks,
Bob

IF and MAX statements
 

Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B1048,A1:A10),2)
That formula returns the 2nd largest value from Col_A where the value in
Col_B is greater than 48.

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:

I am trying to set up a formula that finds the maximum value, the
second-highest value, third-highest value, etc. in a column, depending on
whether the values in another column meet a certain criteria.

In Column C, I want to return the highest value in Column A if the value in
Column B is 48 or higher. Then the second-highest value, third-highest, etc.

Example:
Column A Column B
.100 52
.200 14
.300 32
.400 123
.500 101

Can anyone help?

Thanks,
Bob

IF and MAX statements
 

Ron,

Thanks. That works. Would you be able to help me with this question as well?

Now that I know the highest value in the column, I want the formula to
return a value from that same row in a different column. In the example, Row
5 contains the highest value (.500) and I want the formula to return "New
York" as the answer. I also want to do this for the second-highest value
(where Column B 48, and third-highest, etc.

Example:
Column A Column B Column D
Row 1 .100 52 Chicago
Row 2 .200 14 Cleveland
Row 3 .300 32 Washington
Row 4 .400 123 Washington
Row 5 .500 101 New York

Thanks,
Bob

"Ron Coderre" wrote:

Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B1048,A1:A10),2)
That formula returns the 2nd largest value from Col_A where the value in
Col_B is greater than 48.

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:

I am trying to set up a formula that finds the maximum value, the
second-highest value, third-highest value, etc. in a column, depending on
whether the values in another column meet a certain criteria.

In Column C, I want to return the highest value in Column A if the value in
Column B is 48 or higher. Then the second-highest value, third-highest, etc.

Example:
Column A Column B
.100 52
.200 14
.300 32
.400 123
.500 101

Can anyone help?

Thanks,
Bob

IF and MAX statements
 

Try this:

With data in A1:C10

E1: (the rank to find: 1, 2, etc)

D1: =INDEX(C1:C10,MATCH(LARGE(IF(B1:B1048,A1:A10),E1) ,A1:A10,0))

If E1 contains 2, that formula will return
the city in Col_C that is associated with
the 2nd largest value in Col_A
where the corresponding value in Col_B is greater than 48
Washington

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:

Ron,

Thanks. That works. Would you be able to help me with this question as well?

Now that I know the highest value in the column, I want the formula to
return a value from that same row in a different column. In the example, Row
5 contains the highest value (.500) and I want the formula to return "New
York" as the answer. I also want to do this for the second-highest value
(where Column B 48, and third-highest, etc.

Example:
Column A Column B Column D
Row 1 .100 52 Chicago
Row 2 .200 14 Cleveland
Row 3 .300 32 Washington
Row 4 .400 123 Washington
Row 5 .500 101 New York

Thanks,
Bob

"Ron Coderre" wrote:

Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B1048,A1:A10),2)
That formula returns the 2nd largest value from Col_A where the value in
Col_B is greater than 48.

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:

I am trying to set up a formula that finds the maximum value, the
second-highest value, third-highest value, etc. in a column, depending on
whether the values in another column meet a certain criteria.

In Column C, I want to return the highest value in Column A if the value in
Column B is 48 or higher. Then the second-highest value, third-highest, etc.

Example:
Column A Column B
.100 52
.200 14
.300 32
.400 123
.500 101

Can anyone help?

Thanks,
Bob

IF and MAX statements
 

See for a different approach to build a Top N list:

http://www.excelforum.com/showthread.php?t=333697

bob wrote:
Ron,

Thanks. That works. Would you be able to help me with this question as well?

Now that I know the highest value in the column, I want the formula to
return a value from that same row in a different column. In the example, Row
5 contains the highest value (.500) and I want the formula to return "New
York" as the answer. I also want to do this for the second-highest value
(where Column B 48, and third-highest, etc.

Example:
Column A Column B Column D
Row 1 .100 52 Chicago
Row 2 .200 14 Cleveland
Row 3 .300 32 Washington
Row 4 .400 123 Washington
Row 5 .500 101 New York

Thanks,
Bob

"Ron Coderre" wrote:


Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B1048,A1:A10),2)
That formula returns the 2nd largest value from Col_A where the value in
Col_B is greater than 48.

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:


I am trying to set up a formula that finds the maximum value, the
second-highest value, third-highest value, etc. in a column, depending on
whether the values in another column meet a certain criteria.

In Column C, I want to return the highest value in Column A if the value in
Column B is 48 or higher. Then the second-highest value, third-highest, etc.

Example:
Column A Column B
.100 52
.200 14
.300 32
.400 123
.500 101

Can anyone help?

Thanks,
Bob

IF and MAX statements
 

Sorry, this provides a #NUM! value. The formula I used is

=INDEX(PlayerSummary!$E$2:$E100,MATCH(LARGE(IF(Pla yerSummary!$E$2:$E100=48,PlayerSummary!$AB$2:$AB$ 100),D7),PlayerSummary!$E$2:$E100,0))

I set it up as an array with SHIFT CTRL ENTER.

Here is the actual formula I have that produces the highest-ranking value.
It is in Column D of the current worksheet.

=LARGE(IF(PlayerSummary!$E$2:$E10048,PlayerSummar y!$AB$2:$AB$100),1)

In Column C of the current worksheet, I am trying to place a formula that
produces the corresponding value in PlayerSummary!E2:E100. The
highest-ranking value came from PlayerSummary!AB2:AB100.

My question is, how do i produce the value in PlayerSummary!E2:E100 that
corresponds to the highest-ranking value in PlayerSummary!AB2:AB100?

Thanks,
Bob

"Ron Coderre" wrote:

Try this:

With data in A1:C10

E1: (the rank to find: 1, 2, etc)

D1: =INDEX(C1:C10,MATCH(LARGE(IF(B1:B1048,A1:A10),E1) ,A1:A10,0))

If E1 contains 2, that formula will return
the city in Col_C that is associated with
the 2nd largest value in Col_A
where the corresponding value in Col_B is greater than 48
Washington

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:

Ron,

Thanks. That works. Would you be able to help me with this question as well?

Now that I know the highest value in the column, I want the formula to
return a value from that same row in a different column. In the example, Row
5 contains the highest value (.500) and I want the formula to return "New
York" as the answer. I also want to do this for the second-highest value
(where Column B 48, and third-highest, etc.

Example:
Column A Column B Column D
Row 1 .100 52 Chicago
Row 2 .200 14 Cleveland
Row 3 .300 32 Washington
Row 4 .400 123 Washington
Row 5 .500 101 New York

Thanks,
Bob

"Ron Coderre" wrote:

Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B1048,A1:A10),2)
That formula returns the 2nd largest value from Col_A where the value in
Col_B is greater than 48.

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"bob" wrote:

I am trying to set up a formula that finds the maximum value, the
second-highest value, third-highest value, etc. in a column, depending on
whether the values in another column meet a certain criteria.

In Column C, I want to return the highest value in Column A if the value in
Column B is 48 or higher. Then the second-highest value, third-highest, etc.

Example:
Column A Column B
.100 52
.200 14
.300 32
.400 123
.500 101

Can anyone help?

Thanks,
Bob