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" <RalphSE.274x7m_1146497100.5731@excelforum-nospam.com> wrote in
message news:RalphSE.274x7m_1146497100.5731@excelforum-nospam.com...
>
> 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!
>
>
> --
> RalphSE
> ------------------------------------------------------------------------
> RalphSE's Profile:
> http://www.excelforum.com/member.php...o&userid=29931
> View this thread: http://www.excelforum.com/showthread...hreadid=537759
>

[RalphSE]

Thanks Peo, Seems To Be Working Great!

[RalphSE]

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

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

THANKS!