| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF function with too many arguments | Excel Worksheet Functions | |||
| You've entered too many arguments for this function | Excel Discussion (Misc queries) | |||
| If Function with 3 arguments | Excel Worksheet Functions | |||
| Function Arguments | Excel Worksheet Functions | |||
| Is it possible to use more than 8 arguments in a function? | Excel Discussion (Misc queries) | |||
Linear Mode
