Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help for data mining
Hello Everyone,
I have struggled with this until I have only a few hairs left on the top of head...Can any of you kind souls please help? A B C D E F G DATE TIME HIGH LOW DATE TIME OF HIGH TIME OF LOW 8/29/08 15:55 591.30 590.87 8/29/08 15:35 15:55 8/29/08 15:50 592.12 591.25 8/28/08 15:40 15:30 8/29/08 15:45 592.56 592.17 8/27/08 15:35 15:50 8/29/08 15:40 592.56 591.85 8/29/08 15:35 593.27 592.57 8/29/08 15:30 592.99 592.22 8/28/08 15:55 599.52 598.66 8/28/08 15:50 599.17 598.71 8/28/08 15:45 599.13 598.67 8/28/08 15:40 599.53 599.01 8/28/08 15:35 598.84 598.26 8/28/08 15:30 598.51 597.82 8/27/08 15:55 598.50 597.85 8/27/08 15:50 598.41 597.50 8/27/08 15:45 598.34 597.92 8/27/08 15:40 598.63 597.89 8/27/08 15:35 598.75 598.24 8/27/08 15:30 598.60 597.79 8/26/08 15:55 598.87 598.29 8/26/08 15:50 598.71 598.18 8/26/08 15:45 598.53 598.15 8/26/08 15:40 598.43 598.02 8/26/08 15:35 598.78 598.17 8/27/08 15:30 598.45 598.19 Data entry begins in A1. I need the following: 1. In Column E, return from Column A only one date for each of the dates in Column A (i.e., E2 should return 8/29/08; E3 should return 8/28/08; E4 should return 8/257/08; and so on). 2. Next, from the range of Highs in Column C find the MAXIMUM High for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column F in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding highs in Column C is C2:C7, and the maximum high in this range is 593.27, thus F2 should return the time of 15:35.) 3. Next, from the range of Lows in Column D find the MIMIMUM Low for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column G in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding lows in Column D is D2:D7, and the minimum low in this range is 590.87, thus G2 should return the time of 15:55.) Thanks for any help... -- johnnyk |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help for data mining
Two steps:
1. using a Pivot Table, create a table that gives the max high and min low for each date 2. use VLOOKUP to get the times associated with each vlue in the table above. -- Gary''s Student - gsnu200809 "johnnyk" wrote: Hello Everyone, I have struggled with this until I have only a few hairs left on the top of head...Can any of you kind souls please help? A B C D E F G DATE TIME HIGH LOW DATE TIME OF HIGH TIME OF LOW 8/29/08 15:55 591.30 590.87 8/29/08 15:35 15:55 8/29/08 15:50 592.12 591.25 8/28/08 15:40 15:30 8/29/08 15:45 592.56 592.17 8/27/08 15:35 15:50 8/29/08 15:40 592.56 591.85 8/29/08 15:35 593.27 592.57 8/29/08 15:30 592.99 592.22 8/28/08 15:55 599.52 598.66 8/28/08 15:50 599.17 598.71 8/28/08 15:45 599.13 598.67 8/28/08 15:40 599.53 599.01 8/28/08 15:35 598.84 598.26 8/28/08 15:30 598.51 597.82 8/27/08 15:55 598.50 597.85 8/27/08 15:50 598.41 597.50 8/27/08 15:45 598.34 597.92 8/27/08 15:40 598.63 597.89 8/27/08 15:35 598.75 598.24 8/27/08 15:30 598.60 597.79 8/26/08 15:55 598.87 598.29 8/26/08 15:50 598.71 598.18 8/26/08 15:45 598.53 598.15 8/26/08 15:40 598.43 598.02 8/26/08 15:35 598.78 598.17 8/27/08 15:30 598.45 598.19 Data entry begins in A1. I need the following: 1. In Column E, return from Column A only one date for each of the dates in Column A (i.e., E2 should return 8/29/08; E3 should return 8/28/08; E4 should return 8/257/08; and so on). 2. Next, from the range of Highs in Column C find the MAXIMUM High for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column F in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding highs in Column C is C2:C7, and the maximum high in this range is 593.27, thus F2 should return the time of 15:35.) 3. Next, from the range of Lows in Column D find the MIMIMUM Low for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column G in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding lows in Column D is D2:D7, and the minimum low in this range is 590.87, thus G2 should return the time of 15:55.) Thanks for any help... -- johnnyk |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help for data mining
Thank you for your speedy response. I got step 1 with no problem, but I can't
get step 2 to work properly. Could you provide an example formula to use? -- johnnyk "Gary''s Student" wrote: Two steps: 1. using a Pivot Table, create a table that gives the max high and min low for each date 2. use VLOOKUP to get the times associated with each vlue in the table above. -- Gary''s Student - gsnu200809 "johnnyk" wrote: Hello Everyone, I have struggled with this until I have only a few hairs left on the top of head...Can any of you kind souls please help? A B C D E F G DATE TIME HIGH LOW DATE TIME OF HIGH TIME OF LOW 8/29/08 15:55 591.30 590.87 8/29/08 15:35 15:55 8/29/08 15:50 592.12 591.25 8/28/08 15:40 15:30 8/29/08 15:45 592.56 592.17 8/27/08 15:35 15:50 8/29/08 15:40 592.56 591.85 8/29/08 15:35 593.27 592.57 8/29/08 15:30 592.99 592.22 8/28/08 15:55 599.52 598.66 8/28/08 15:50 599.17 598.71 8/28/08 15:45 599.13 598.67 8/28/08 15:40 599.53 599.01 8/28/08 15:35 598.84 598.26 8/28/08 15:30 598.51 597.82 8/27/08 15:55 598.50 597.85 8/27/08 15:50 598.41 597.50 8/27/08 15:45 598.34 597.92 8/27/08 15:40 598.63 597.89 8/27/08 15:35 598.75 598.24 8/27/08 15:30 598.60 597.79 8/26/08 15:55 598.87 598.29 8/26/08 15:50 598.71 598.18 8/26/08 15:45 598.53 598.15 8/26/08 15:40 598.43 598.02 8/26/08 15:35 598.78 598.17 8/27/08 15:30 598.45 598.19 Data entry begins in A1. I need the following: 1. In Column E, return from Column A only one date for each of the dates in Column A (i.e., E2 should return 8/29/08; E3 should return 8/28/08; E4 should return 8/257/08; and so on). 2. Next, from the range of Highs in Column C find the MAXIMUM High for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column F in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding highs in Column C is C2:C7, and the maximum high in this range is 593.27, thus F2 should return the time of 15:35.) 3. Next, from the range of Lows in Column D find the MIMIMUM Low for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column G in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding lows in Column D is D2:D7, and the minimum low in this range is 590.87, thus G2 should return the time of 15:55.) Thanks for any help... -- johnnyk |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help for data mining
To Gary's Student & Don Guillett,
I want to thank you both for your speedy responses to my dilemma. By combining the suggestions from both of you along with a lot of experimenting, I was able to get the results I desired. But because I am learning thru the trial-and-error method, my worksheet may not be as efficient as it could be...nevertheless it works. Thanks a million to both of you! -- johnnyk "Don Guillett" wrote: 08/29/08 590.87 15:55 08/28/08 597.82 15:30 08/27/08 597.50 15:50 08/26/08 598.02 15:40 =MIN(IF($A$2:$A$25=$E6,$D$2:$D$25)) =MIN(IF(($A$2:$A$25=$E6)*($D$2:$D$25=$F6),$B$2:$B$ 25)) These are ARRAY formulas that must be entered using ctrl+shift+enter -- Don Guillett Microsoft MVP Excel SalesAid Software "johnnyk" wrote in message ... Hello Everyone, I have struggled with this until I have only a few hairs left on the top of head...Can any of you kind souls please help? A B C D E F G DATE TIME HIGH LOW DATE TIME OF HIGH TIME OF LOW 8/29/08 15:55 591.30 590.87 8/29/08 15:35 15:55 8/29/08 15:50 592.12 591.25 8/28/08 15:40 15:30 8/29/08 15:45 592.56 592.17 8/27/08 15:35 15:50 8/29/08 15:40 592.56 591.85 8/29/08 15:35 593.27 592.57 8/29/08 15:30 592.99 592.22 8/28/08 15:55 599.52 598.66 8/28/08 15:50 599.17 598.71 8/28/08 15:45 599.13 598.67 8/28/08 15:40 599.53 599.01 8/28/08 15:35 598.84 598.26 8/28/08 15:30 598.51 597.82 8/27/08 15:55 598.50 597.85 8/27/08 15:50 598.41 597.50 8/27/08 15:45 598.34 597.92 8/27/08 15:40 598.63 597.89 8/27/08 15:35 598.75 598.24 8/27/08 15:30 598.60 597.79 8/26/08 15:55 598.87 598.29 8/26/08 15:50 598.71 598.18 8/26/08 15:45 598.53 598.15 8/26/08 15:40 598.43 598.02 8/26/08 15:35 598.78 598.17 8/27/08 15:30 598.45 598.19 Data entry begins in A1. I need the following: 1. In Column E, return from Column A only one date for each of the dates in Column A (i.e., E2 should return 8/29/08; E3 should return 8/28/08; E4 should return 8/257/08; and so on). 2. Next, from the range of Highs in Column C find the MAXIMUM High for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column F in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding highs in Column C is C2:C7, and the maximum high in this range is 593.27, thus F2 should return the time of 15:35.) 3. Next, from the range of Lows in Column D find the MIMIMUM Low for each date in Column A that matches the Date in Column E, and then return its corresponding Time in column G in the same row as the matched date. (i.e., the range of dates in Column A for 8/29/08 is A2:A7, and the range of corresponding lows in Column D is D2:D7, and the minimum low in this range is 590.87, thus G2 should return the time of 15:55.) Thanks for any help... -- johnnyk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Save 20% On Data Conversion and Data Formats Services by Data EntryIndia | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |