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