MIN Function with arguments
 

Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Hi,

In cell D1, use the following function - =MAX(IF(($A$1:$A$8=C1),$B$1:$B$8)).
Confirm the formula with a Ctrl+Shift+Enter. In cell E1, enter the
following formula - =MIN(IF(($A$1:$A$8=C1),$B$1:$B$8)). Confirm the formula
with a Ctrl+Shift+Enter.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Freshman" wrote in message
...
Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for
the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1?
Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Assume data as posted within A1:B8

Input in C1, eg: Peter
In D1, array-entered*: =MAX(IF(A1:A8=C1,B1:B8))
In E1, array-entered*: =MIN(IF(A1:A8=C1,B1:B8))
Adapt the ranges to suit your actual data extents

*Press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---
"Freshman" wrote:
In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Try

=MIN(IF($A$1:$A$8=$C$1,$B$1:$B$8,FALSE))
=MAX(IF($A$1:$A$8=$C$1,$B$1:$B$8,FALSE))

Both of these ar array formula so commit with CTRL+Shift+Enter NOT just
enter. If you do it correctly Excel will put curly brackets around the
formula {}. You can't type these yourself.

Mike

"Freshman" wrote:

Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

In D1: =MAX(INDEX((A1:A8=C1)*B1:B8,))
In E1: =MIN(INDEX(10^10-(A1:A8=C1)*(10^10-B1:B8),))

just press ENTER


"Freshman" wrote:

Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Hi Max,

Long time no see. Thanks for your tips again. Best regards to you and your
family.

"Max" wrote:

Assume data as posted within A1:B8

Input in C1, eg: Peter
In D1, array-entered*: =MAX(IF(A1:A8=C1,B1:B8))
In E1, array-entered*: =MIN(IF(A1:A8=C1,B1:B8))
Adapt the ranges to suit your actual data extents

*Press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---
"Freshman" wrote:
In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Hi Ashish,

Thanks for your tips. Have a nice day.

"Ashish Mathur" wrote:

Hi,

In cell D1, use the following function - =MAX(IF(($A$1:$A$8=C1),$B$1:$B$8)).
Confirm the formula with a Ctrl+Shift+Enter. In cell E1, enter the
following formula - =MIN(IF(($A$1:$A$8=C1),$B$1:$B$8)). Confirm the formula
with a Ctrl+Shift+Enter.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Freshman" wrote in message
...
Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for
the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1?
Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Hi Mike,

Nice with your help and tips. Good day. Cheers.

"Mike H" wrote:

Try

=MIN(IF($A$1:$A$8=$C$1,$B$1:$B$8,FALSE))
=MAX(IF($A$1:$A$8=$C$1,$B$1:$B$8,FALSE))

Both of these ar array formula so commit with CTRL+Shift+Enter NOT just
enter. If you do it correctly Excel will put curly brackets around the
formula {}. You can't type these yourself.

Mike

"Freshman" wrote:

Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Hi Mama,

I love your name and thanks for your help. Good health and good luck to you.
Bye.

"Teethless mama" wrote:

In D1: =MAX(INDEX((A1:A8=C1)*B1:B8,))
In E1: =MIN(INDEX(10^10-(A1:A8=C1)*(10^10-B1:B8),))

just press ENTER


"Freshman" wrote:

Dear experts,

In a worksheet, column A is for player's name and column B is for player's
scores. I list some examples below:

John 25
Peter 65
May 37
Tim 81
Peter 50
Peter 71
May 68
John 57

In C1 is for a lookup value, D1 is for the MAX value of C1 and E1 is for the
MIN value of C1. So, when I input Peter in C1, then D1 will show 71 and E1
will show 50. In such a case, what should be the formula in D1 and E1? Please
kindly advise.

Thanks in advance.

MIN Function with arguments
 

Welcome, Freshman
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---
"Freshman" wrote in message
...
Hi Max,

Long time no see. Thanks for your tips again. Best regards to you and your
family.