Conditional Formatting Formula Help

[RalphSE]

Hi,

I am trying to write a formula to be placed in the conditional
formatting to color code certain cells but am having some trouble
coming up with the formula. Basically I have about 240 rows of data,
the columns are by date. What I want is for whenever a daily reading
is above the 20 day average of todays data and the 19 previous cells of
data it will be green; and when below its 20 day average, red. The
formula will start in column W, row 2. So the formula should be
something like (in laymans language) "IF W2 > AVERAGE(D2:W2)" FOR GREEN
and "IF W2 < AVERAGE(D2:W2)" for red. Can someone please help me with
the formula?

Thanks!

 

[Peo Sjoblom]

Assuming you mean that the columns will increase so that what is W2 will be
X2 next day

=AVERAGE(IV2:INDEX(2:2,SUMPRODUCT(LARGE(COLUMN(2:2)*(2:2<>""),20))))>W2

in the formula is part, format to your liking, then add another condition
and reverse the > to

=AVERAGE(IV2:INDEX(2:2,SUMPRODUCT(LARGE(COLUMN(2:2)*(2:2<>""),20))))<W2


copy the format across using for example the format painter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[RalphSE]

Thanks Peo, Seems To Be Working Great!

 

[RalphSE]

yikes! sorry, I realized the situation is a little more complicate
than I thought, actually the formula needs to be in a differen
worksheet and reference the original worksheet, I hope this can b
done, so here's how the formula needs to be:

in cell X2 of worksheet "10 DAY AVERAGES" I need a formula that test
cell W2 in worksheet "PASTE DATA" to see if it's greater than the 2
day average of cells W2 through D2 in worksheet "PASTE DATA", and if s
I will program a certain highlighting color via conditional formatting
I would appreciate very much some help with this formula

THANKS