![]() |
Show min and max
Hello,
Thanks in advance for the help. In column A I have drivers names listed, their names are listed multiple times. In column B I have odometer reading from each time they fill up with gas. So the sheet will look like this: NELSON 16977 PIN 13484 COED 20180 KING 44480 VAN 57243 COED 20790 COED 20419 NELSON 17280 ANGELL 13920 POGG 8266 GILLES 17254 KING 44611 On another sheet I have the drivers names listed in column A (their names are only listed once on this sheet. In column B I would like a formula that will match the name and return the odometer's min number and in column C return the odometer's max number. I appreciate the help. Thank you very much. Jim |
Show min and max
Jim,
This assumes your list of names/odometer readings are on sheet 1 and the name you want is in a1 of the active sheet. =MIN(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12)) =MAX(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12)) Both of these are array formula so look below on how to enter This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Jim" wrote: Hello, Thanks in advance for the help. In column A I have drivers names listed, their names are listed multiple times. In column B I have odometer reading from each time they fill up with gas. So the sheet will look like this: NELSON 16977 PIN 13484 COED 20180 KING 44480 VAN 57243 COED 20790 COED 20419 NELSON 17280 ANGELL 13920 POGG 8266 GILLES 17254 KING 44611 On another sheet I have the drivers names listed in column A (their names are only listed once on this sheet. In column B I would like a formula that will match the name and return the odometer's min number and in column C return the odometer's max number. I appreciate the help. Thank you very much. Jim |
Show min and max
Mike,
I'm getting #N/A, does it make a difference that part of the lookup range will have blanks or other formula's "Mike H" wrote: Jim, This assumes your list of names/odometer readings are on sheet 1 and the name you want is in a1 of the active sheet. =MIN(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12)) =MAX(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12)) Both of these are array formula so look below on how to enter This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Jim" wrote: Hello, Thanks in advance for the help. In column A I have drivers names listed, their names are listed multiple times. In column B I have odometer reading from each time they fill up with gas. So the sheet will look like this: NELSON 16977 PIN 13484 COED 20180 KING 44480 VAN 57243 COED 20790 COED 20419 NELSON 17280 ANGELL 13920 POGG 8266 GILLES 17254 KING 44611 On another sheet I have the drivers names listed in column A (their names are only listed once on this sheet. In column B I would like a formula that will match the name and return the odometer's min number and in column C return the odometer's max number. I appreciate the help. Thank you very much. Jim |
Show min and max
Jim,
Try as I might I can't make that formula produce #N/A and no it doesn't matter if there are formula in the lookup range Post the precise formula you used. Mike "Jim" wrote: Mike, I'm getting #N/A, does it make a difference that part of the lookup range will have blanks or other formula's "Mike H" wrote: Jim, This assumes your list of names/odometer readings are on sheet 1 and the name you want is in a1 of the active sheet. =MIN(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12)) =MAX(IF(Sheet1!$A$1:$A$12=A1,Sheet1!$B$1:$B$12)) Both of these are array formula so look below on how to enter This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Jim" wrote: Hello, Thanks in advance for the help. In column A I have drivers names listed, their names are listed multiple times. In column B I have odometer reading from each time they fill up with gas. So the sheet will look like this: NELSON 16977 PIN 13484 COED 20180 KING 44480 VAN 57243 COED 20790 COED 20419 NELSON 17280 ANGELL 13920 POGG 8266 GILLES 17254 KING 44611 On another sheet I have the drivers names listed in column A (their names are only listed once on this sheet. In column B I would like a formula that will match the name and return the odometer's min number and in column C return the odometer's max number. I appreciate the help. Thank you very much. Jim |
All times are GMT +1. The time now is 10:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com