Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
Good Day:
Data Input is as follows: A B C Date Time Value 09/01/2006 19:25:37 8.89 09/01/2006 19:29:37 8.23 09/01/2006 19:33:37 8.14 09/01/2006 19:37:37 8.22 . . . . . . The data set is quite large and is continuous for one month of data. I would like to get an output that looks like the following (on a sepearate worksheet): A B C Date Min. Max. 09/01/2006 8.14 8.89 09/02/2006 6.54 9.76 09/03/2006 7.01 8.46 . . . . . . Thanks in advance for your assistance. DDD |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
On sheet 2
B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) ctrlshiftenter (not just enter) Highlight B2 & C2 and copy down as far as needed "DDD" wrote: Good Day: Data Input is as follows: A B C Date Time Value 09/01/2006 19:25:37 8.89 09/01/2006 19:29:37 8.23 09/01/2006 19:33:37 8.14 09/01/2006 19:37:37 8.22 . . . . . . The data set is quite large and is continuous for one month of data. I would like to get an output that looks like the following (on a sepearate worksheet): A B C Date Min. Max. 09/01/2006 8.14 8.89 09/02/2006 6.54 9.76 09/03/2006 7.01 8.46 . . . . . . Thanks in advance for your assistance. DDD |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
Thanks mama, but I guess I was not clear enough.
The input data is not finite and varies day to day month to month. What I was looking for was how to search all of the 09/01/06 entries (between 300 and 1000 entries) for a max and min, then write that date and it's corresponding min/max values - then continue to the next date in the data 09/02/06 and search all that date for min/max - write that date and the min/max --- continue until the data runs out about 10K to 13K entries per month... Does that make sense? Take care "Teethless mama" wrote: On sheet 2 B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) ctrlshiftenter (not just enter) Highlight B2 & C2 and copy down as far as needed "DDD" wrote: Good Day: Data Input is as follows: A B C Date Time Value 09/01/2006 19:25:37 8.89 09/01/2006 19:29:37 8.23 09/01/2006 19:33:37 8.14 09/01/2006 19:37:37 8.22 . . . . . . The data set is quite large and is continuous for one month of data. I would like to get an output that looks like the following (on a sepearate worksheet): A B C Date Min. Max. 09/01/2006 8.14 8.89 09/02/2006 6.54 9.76 09/03/2006 7.01 8.46 . . . . . . Thanks in advance for your assistance. DDD |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
DDD wrote...
.... The input data is not finite and varies day to day month to month. What I Maybe your data isn't 'finite' theoretically, but you can only fit a finite amount into any Excel worksheet. Perhaps you mean it could fill an entire column from row 2 down to row 65536. I'll assume that's what you meant. was looking for was how to search all of the 09/01/06 entries (between 300 and 1000 entries) for a max and min, then write that date and it's corresponding min/max values - then continue to the next date in the data 09/02/06 and search all that date for min/max - write that date and the min/max --- continue until the data runs out about 10K to 13K entries per month... Does that make sense? .... "Teethless mama" wrote: B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) You could use the suggested approach, just adjust the range references, e.g., B2: =MIN(IF(Sheet1!$A$2:$A$65536=$A2,Sheet1!$C$2:$C$65 536)) and similarly for the MAX in C2. Adjust the range references in the formulas above as needed. Since you haven't specified *YOUR* actual ranges or worksheet names, that task is left to YOU unless you provide those additional details. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
Harlan:
That did the trick. Perfect... Now, if I wanted to search sheet one (pH Readings) for all 09/01/06 values, find the min and max and place the answer in sheet two (Min-Max) - DATE , MIN, MAX and then continue through all the dates available - could I not use a lookup, or an Index/Match function ? Take ca ddd "Harlan Grove" wrote: DDD wrote... .... The input data is not finite and varies day to day month to month. What I Maybe your data isn't 'finite' theoretically, but you can only fit a finite amount into any Excel worksheet. Perhaps you mean it could fill an entire column from row 2 down to row 65536. I'll assume that's what you meant. was looking for was how to search all of the 09/01/06 entries (between 300 and 1000 entries) for a max and min, then write that date and it's corresponding min/max values - then continue to the next date in the data 09/02/06 and search all that date for min/max - write that date and the min/max --- continue until the data runs out about 10K to 13K entries per month... Does that make sense? .... "Teethless mama" wrote: B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5)) You could use the suggested approach, just adjust the range references, e.g., B2: =MIN(IF(Sheet1!$A$2:$A$65536=$A2,Sheet1!$C$2:$C$65 536)) and similarly for the MAX in C2. Adjust the range references in the formulas above as needed. Since you haven't specified *YOUR* actual ranges or worksheet names, that task is left to YOU unless you provide those additional details. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
DDD wrote...
.... Now, if I wanted to search sheet one (pH Readings) for all 09/01/06 values, find the min and max and place the answer in sheet two (Min-Max) - DATE , MIN, MAX and then continue through all the dates available - could I not use a lookup, or an Index/Match function ? .... You *could* use lookup or index/match calls, but there's nothing gained by doing so. MIN/IF and MAX/IF are all you need. This assumes you've already entered dates in your second worksheet. If so, then you use those date entries in the second worksheet in the conditional terms (first arguments) of the IF function calls to compare against the entire column of dates in the first worksheet, and you use the value range in the first worksheet as the second argument of the IF function calls. This produces an array of values from the first worksheet corresponding to the dates from the first worksheet that match the given date from the second worksheet, and FALSE for nonmatching dates. Enclosing MIN and MAX calls then return the minimum and maximum values for the given date. No need for lookups. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Easy for the experts: Lookup,index,match....
Harlan:
Thank you so much for your help and advice. Take care ddd "Harlan Grove" wrote: DDD wrote... .... Now, if I wanted to search sheet one (pH Readings) for all 09/01/06 values, find the min and max and place the answer in sheet two (Min-Max) - DATE , MIN, MAX and then continue through all the dates available - could I not use a lookup, or an Index/Match function ? .... You *could* use lookup or index/match calls, but there's nothing gained by doing so. MIN/IF and MAX/IF are all you need. This assumes you've already entered dates in your second worksheet. If so, then you use those date entries in the second worksheet in the conditional terms (first arguments) of the IF function calls to compare against the entire column of dates in the first worksheet, and you use the value range in the first worksheet as the second argument of the IF function calls. This produces an array of values from the first worksheet corresponding to the dates from the first worksheet that match the given date from the second worksheet, and FALSE for nonmatching dates. Enclosing MIN and MAX calls then return the minimum and maximum values for the given date. No need for lookups. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Then formula - Should be easy!?! | Excel Discussion (Misc queries) | |||
Easy question? Page numbers | Excel Discussion (Misc queries) | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
new user with easy question? not easy for me | New Users to Excel |