IF and MAX statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B10>48,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
 
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 said:
Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B10>48,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 said:
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
Click to expand...
Click to expand...
 
Try this:

With data in A1:C10

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

D1: =INDEX(C1:C10,MATCH(LARGE(IF(B1:B10>48,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
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

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

XL2002, WinXP-Pro


bob said:
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 said:
Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B10>48,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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
See for a different approach to build a Top N list:

http://www.excelforum.com/showthread.php?t=333697
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

:

Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B10>48,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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Sorry, this provides a #NUM! value. The formula I used is

=INDEX(PlayerSummary!$E$2:$E100,MATCH(LARGE(IF(PlayerSummary!$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:$E100>48,PlayerSummary!$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 said:
Try this:

With data in A1:C10

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

D1: =INDEX(C1:C10,MATCH(LARGE(IF(B1:B10>48,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
Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

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

XL2002, WinXP-Pro


bob said:
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 said:
Try something like this:

With values in A1:B10

C1: =LARGE(IF(B1:B10>48,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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top