How do I Hi- lite the max of last 20 entries

  • Thread starter Thread starter Joe Goz
  • Start date Start date
J

Joe Goz

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?
 
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.
 
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
 
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>=LARGE(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>=LARGE(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))
 
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
 
Back
Top