| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Challenge
Ok, This is a tuffy. I have a spreadsheet that has the hourly data for two stocks (Stock A minus Stock B). This is called a spread. Each line represents 1 hour of data. The columns look like this: Date Open High Low Close 12/21/2005 -0.6 -0.38 -0.69 -0.3 12/21/2005 -0.3 -0.27 -0.45 -0.4 12/21/2005 -0.41 -0.41 -0.54 -0.55 12/21/2005 -0.54 -0.48 -0.33 -0.31 12/21/2005 -0.29 -0.35 -0.39 -0.45 12/21/2005 -0.44 -0.45 -0.35 -0.51 12/21/2005 -0.5 -0.53 -0.49 -0.59 12/22/2005 -0.73 -0.54 -0.58 -0.34 12/22/2005 -0.35 -0.35 -0.18 -0.17 12/22/2005 -0.17 -0.14 0.03 0.12 12/22/2005 0.1 0.03 0.13 0.14 12/22/2005 0.14 0.12 -0.08 -0.08 12/22/2005 -0.08 0.02 0.04 0.01 12/22/2005 0.02 -0.01 0.02 0.03 12/23/2005 0.36 0.02 -0.24 -0.18 12/23/2005 -0.17 0.03 -0.1 -0.06 12/23/2005 -0.07 -0.03 -0.06 -0.03 12/23/2005 -0.05 0.02 -0.02 0.01 12/23/2005 -0.01 0.02 0.01 0.02 12/23/2005 0.01 0.07 -0.01 0 12/23/2005 0 0.03 0 0.03 As you can see for each day there are 7 entries. I need to find the highest high and the lowest low for each day and have it export in order by data so that I get something that looks like this............. Date Open High Low Close 12/8/2005 0.1 0.51 -0.04 0.26 12/9/2005 0.18 0.42 -0.04 0.28 12/12/2005 0.35 0.76 0.17 0.73 12/13/2005 0.85 0.85 -0.33 -0.18 12/14/2005 -0.13 -0.13 -0.75 -0.55 12/15/2005 -0.58 -0.42 -0.82 -0.44 12/16/2005 -0.56 -0.46 -0.91 -0.57 Thank you!! -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
It looks like a job for Data|pivottable. You can bring in the max or min of any
field. Or you could use Data|subtotals. Use Max as the function for both fields. And use each change in the Date column. You'll end up with formulas like: =subtotal(4,c2:c9) The 4 means max. Then select the column that should have the minimums. Edit|replace what: =subtotal(4, with: =subtotal(5, replace all (5 means min.) Then you can use the outlining symbols on the left to hide/show the details. If you need to save just the subtotal rows: hide the details select the range edit|goto|special|visible cells only edit|copy edit|paste (at a new location) jimbob wrote: Ok, This is a tuffy. I have a spreadsheet that has the hourly data for two stocks (Stock A minus Stock B). This is called a spread. Each line represents 1 hour of data. The columns look like this: Date Open High Low Close 12/21/2005 -0.6 -0.38 -0.69 -0.3 12/21/2005 -0.3 -0.27 -0.45 -0.4 12/21/2005 -0.41 -0.41 -0.54 -0.55 12/21/2005 -0.54 -0.48 -0.33 -0.31 12/21/2005 -0.29 -0.35 -0.39 -0.45 12/21/2005 -0.44 -0.45 -0.35 -0.51 12/21/2005 -0.5 -0.53 -0.49 -0.59 12/22/2005 -0.73 -0.54 -0.58 -0.34 12/22/2005 -0.35 -0.35 -0.18 -0.17 12/22/2005 -0.17 -0.14 0.03 0.12 12/22/2005 0.1 0.03 0.13 0.14 12/22/2005 0.14 0.12 -0.08 -0.08 12/22/2005 -0.08 0.02 0.04 0.01 12/22/2005 0.02 -0.01 0.02 0.03 12/23/2005 0.36 0.02 -0.24 -0.18 12/23/2005 -0.17 0.03 -0.1 -0.06 12/23/2005 -0.07 -0.03 -0.06 -0.03 12/23/2005 -0.05 0.02 -0.02 0.01 12/23/2005 -0.01 0.02 0.01 0.02 12/23/2005 0.01 0.07 -0.01 0 12/23/2005 0 0.03 0 0.03 As you can see for each day there are 7 entries. I need to find the highest high and the lowest low for each day and have it export in order by data so that I get something that looks like this............. Date Open High Low Close 12/8/2005 0.1 0.51 -0.04 0.26 12/9/2005 0.18 0.42 -0.04 0.28 12/12/2005 0.35 0.76 0.17 0.73 12/13/2005 0.85 0.85 -0.33 -0.18 12/14/2005 -0.13 -0.13 -0.75 -0.55 12/15/2005 -0.58 -0.42 -0.82 -0.44 12/16/2005 -0.56 -0.46 -0.91 -0.57 Thank you!! -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
BTW. Please try to use meaningful subject lines in your subject
-- Don Guillett SalesAid Software "jimbob" wrote in message ... Ok, This is a tuffy. I have a spreadsheet that has the hourly data for two stocks (Stock A minus Stock B). This is called a spread. Each line represents 1 hour of data. The columns look like this: Date Open High Low Close 12/21/2005 -0.6 -0.38 -0.69 -0.3 12/21/2005 -0.3 -0.27 -0.45 -0.4 12/21/2005 -0.41 -0.41 -0.54 -0.55 12/21/2005 -0.54 -0.48 -0.33 -0.31 12/21/2005 -0.29 -0.35 -0.39 -0.45 12/21/2005 -0.44 -0.45 -0.35 -0.51 12/21/2005 -0.5 -0.53 -0.49 -0.59 12/22/2005 -0.73 -0.54 -0.58 -0.34 12/22/2005 -0.35 -0.35 -0.18 -0.17 12/22/2005 -0.17 -0.14 0.03 0.12 12/22/2005 0.1 0.03 0.13 0.14 12/22/2005 0.14 0.12 -0.08 -0.08 12/22/2005 -0.08 0.02 0.04 0.01 12/22/2005 0.02 -0.01 0.02 0.03 12/23/2005 0.36 0.02 -0.24 -0.18 12/23/2005 -0.17 0.03 -0.1 -0.06 12/23/2005 -0.07 -0.03 -0.06 -0.03 12/23/2005 -0.05 0.02 -0.02 0.01 12/23/2005 -0.01 0.02 0.01 0.02 12/23/2005 0.01 0.07 -0.01 0 12/23/2005 0 0.03 0 0.03 As you can see for each day there are 7 entries. I need to find the highest high and the lowest low for each day and have it export in order by data so that I get something that looks like this............. Date Open High Low Close 12/8/2005 0.1 0.51 -0.04 0.26 12/9/2005 0.18 0.42 -0.04 0.28 12/12/2005 0.35 0.76 0.17 0.73 12/13/2005 0.85 0.85 -0.33 -0.18 12/14/2005 -0.13 -0.13 -0.75 -0.55 12/15/2005 -0.58 -0.42 -0.82 -0.44 12/16/2005 -0.56 -0.46 -0.91 -0.57 Thank you!! -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
Neither of those solutions works. The max and min could be in any of the 4 columns......that's the problem. Any other solutions? -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
I think if you had stated that portion in the original message, you wouldn't
have gotten those replies. I'd use helper columns and put =min(b2:e2) and =max(b2:e2) and drag down. Then use data|subtotals for just those two columns. But you could use Don's formula, too: =max(IF($A$2:$A$22=F2,$b$2:$e$22)) Still an array formula. jimbob wrote: Neither of those solutions works. The max and min could be in any of the 4 columns......that's the problem. Any other solutions? -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
I can't get it to work. Maybe I wasn't clear, so here goes...... Col A has the Date, Col B has the Open for that hour, Col C has the High (ONLY for that hour!), Col D has the Low (ONLY for that hour) and Col E has the Close for that hour. I want to find the Low for the DAY and the High for the DAY. To do this, all 7 lines for each day must be compared to find the lowest low and highest high. I am not that well versed in Excel, so let me try to say it the way I would describe it to someone.... "For all the entries that have the same date, find the lowest low and highest high in cells B2 thru E8, then export those values into another col or worksheet with the appropriate date". *Note that the next date starts on row 9, then the third date starts on row 16 and so on. There are 7 rows of data for each DAY. Thanks again for all your help, I really appreciate it. -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
So what did you try?
jimbob wrote: I can't get it to work. Maybe I wasn't clear, so here goes...... Col A has the Date, Col B has the Open for that hour, Col C has the High (ONLY for that hour!), Col D has the Low (ONLY for that hour) and Col E has the Close for that hour. I want to find the Low for the DAY and the High for the DAY. To do this, all 7 lines for each day must be compared to find the lowest low and highest high. I am not that well versed in Excel, so let me try to say it the way I would describe it to someone.... "For all the entries that have the same date, find the lowest low and highest high in cells B2 thru E8, then export those values into another col or worksheet with the appropriate date". *Note that the next date starts on row 9, then the third date starts on row 16 and so on. There are 7 rows of data for each DAY. Thanks again for all your help, I really appreciate it. -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A Challenge
I agree with Dave but I still think you want the highest HIGH and the lowest
LOW. Probably the same either way, but??? Give us an example of the CORRECT answer(s) for you . -- Don Guillett SalesAid Software "jimbob" wrote in message ... I can't get it to work. Maybe I wasn't clear, so here goes...... Col A has the Date, Col B has the Open for that hour, Col C has the High (ONLY for that hour!), Col D has the Low (ONLY for that hour) and Col E has the Close for that hour. I want to find the Low for the DAY and the High for the DAY. To do this, all 7 lines for each day must be compared to find the lowest low and highest high. I am not that well versed in Excel, so let me try to say it the way I would describe it to someone.... "For all the entries that have the same date, find the lowest low and highest high in cells B2 thru E8, then export those values into another col or worksheet with the appropriate date". *Note that the next date starts on row 9, then the third date starts on row 16 and so on. There are 7 rows of data for each DAY. Thanks again for all your help, I really appreciate it. -- jimbob ------------------------------------------------------------------------ jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107 View this thread: http://www.excelforum.com/showthread...hreadid=527714 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A real challenge for you!! | Excel Discussion (Misc queries) | |||
| conditional formatting challenge | Excel Discussion (Misc queries) | |||
| Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
| Conform a total to a list of results? | Excel Worksheet Functions | |||
| MATCH FUNCTION?...challenge | Excel Worksheet Functions | |||
Hybrid Mode
