Good thought there using the date column and its ascendency to establish the
high boundaries! That definitely shortens the formula from that long mess I
posted just to accomplish the same thing.

Rick


If a new entry is made daily I'm assuming dates are entered in column A in
ascending order.

For the high value of the last 10 (column B = high)

Consition 1
=AND(A1=LARGE(A$1:A$30,10),B1=MAX((A$1:A$30=LARG E(A$1:A$30,10))*B$1:B$30))

For the high value of the last 20:

Condition 2
=AND(A1=LARGE(A$1:A$30,20),B1=MAX((A$1:A$30=LARG E(A$1:A$30,20))*B$1:B$30))

This will also "find" the high 10 first if both the high 20 and the high
10 are one and the same.

The formulas for the low values would follow the same syntax but you'd
replace MAX with MIN and adjust for the range.

I came up with another method that uses dynamic ranges but you need a
total of 8 named formulas, 4 each for the high range and low range. That
leads to a very short CF formula.

=AND(InHRng10,B1=MAX(HRng10))
=AND(InHRng20,B1=MAX(HRng20))
=AND(InLRng10,C1=MAX(LRng10))
=AND(InLRng20,C1=MAX(LRng20))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a method for you, but I find it hard to believe there isn't a more
efficient set of formulas than what I have come up with. (Translation...
check back into this thread from time to time to see if anyone has posted
something better.<g)

Click the header for the column containing your High Values in order to
select this entire column. Next, click Format/Conditional Formatting from
Excel's menu bar. Change the first drop-down field to "Formula Is" and
then copy/paste this formula into the second field...

=AND(ROW(A1)=SUMPRODUCT(MAX(ROW($A$1:$A$1000)*($A $1:$A$1000<"")))-10,A1=MAX(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW($A$1 :$A$1000)*($A$1:$A$1000<"")))-10,1)&":"&ADDRESS(SUMPRODUCT(MAX(ROW($A$1:$A$1000) *($A$1:$A$1000<""))),1))))

Then click the Format button and select the format you want for the
10-day high. Click OK to get back to the Conditional Formatting dialog
box and click the Add button there. For Condition 2 (what appeared when
you clicked the Add button), change its first drop-down field to "Formula
Is" and then copy/paste this formula into its second field...

=AND(ROW(A1)=SUMPRODUCT(MAX(ROW($A$1:$A$1000)*($A $1:$A$1000<"")))-20,A1=MAX(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW($A$1 :$A$1000)*($A$1:$A$1000<"")))-20,1)&":"&ADDRESS(SUMPRODUCT(MAX(ROW($A$1:$A$1000) *($A$1:$A$1000<""))),1))))

Note: The above two formulas are designed for Column A; if you need to
use them in another column, change all the A's to that column's letter(s)
designation **and** change both appearances of ",1" (no quote marks are
in the formulas themselves, I just used them to group the two characters)
to a comma followed by the column's numerical index.

Now, click the Format button and select the format you want for the
20-day high.Then click OK to get back to the Conditional Formatting
dialog box and, finally, click its OK button to activate the conditional
format you just selected. Your 10-day high will have the format you
selected for it and your 20-day high will have whatever format you chose
for it. If the 10-day high is higher than any values in the 11th through
20th day, then only format (the 10-day one) will be displayed (we could
probably add a third conditional format for this if you absolute need
it).

Now, you would do exactly the same procedure for the 10-day and 20-day
lows, but use these formulas where indicated...

Condition 1:
=AND(ROW(B1)=SUMPRODUCT(MAX(ROW($B$1:$B$1000)*($B $1:$B$1000<"")))-10,B1=MIN(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW($B$1 :$B$1000)*($B$1:$B$1000<"")))-10,2)&":"&ADDRESS(SUMPRODUCT(MAX(ROW($B$1:$B$1000) *($B$1:$B$1000<""))),2))))

Condition 2:
=AND(ROW(B1)=SUMPRODUCT(MAX(ROW($B$1:$B$1000)*($B $1:$B$1000<"")))-20,B1=MIN(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW($B$1 :$B$1000)*($B$1:$B$1000<"")))-20,2)&":"&ADDRESS(SUMPRODUCT(MAX(ROW($B$1:$B$1000) *($B$1:$B$1000<""))),2))))

Note: The above two formulas are designed for Column B; if you need to
use them in another column, change all the B's to that column's letter(s)
designation **and** change both appearances of ",2" (no quote marks are
in the formulas themselves, I just used them to group the two characters)
to a comma followed by the column's numerical index.

Rick


"Joe Goz" wrote in message
...
Yes there will always be at least 20 entries. It is possible that the 20
day
and the 10 day High could be the same cell. I guess it is possible to
only
watch There are no empty or blank cells.

"T. Valko" wrote:

Will you *always* have at least 20 entries? If there aren't at least 20
entries then you can't have a 20 day hi/low. Also, how can you
distinguish
the 20 day hi/low from the 10 day hi/low? Seems to me (without a more
detailed explanation) they could be one and the same.

Are there any empty/blank cells within your range of data?

--
Biff
Microsoft Excel MVP


"Joe Goz" <Joe wrote in message
...
I'm using Excel to track price quotes that are entered once a day. I
need
to
Hi-Lite the 10 day Hi and 20 day Hi in one column, and the 10 day
low and
20
day low in another column. The Hi and Low are to seperate columns.