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.

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.

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.

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.

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.

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.