Simplifying a Conditional Formatting Statement

I am looking to conditionally format cells to look for trends in data.

The first trend would be to conditionally format if there are 9 cells in a row either greater than or less than the mean (in D4) (data is arraigned in column F)

This formula would be
=AND(ISNUMBER(F19),OR(AND(F19<$D$4,F18<$D$4,F17<$D$4,F16<$D$4, F15<$D$4,F14<$D$4, F13<$D$4,F12<$D$4,F11<$D$4), AND(F19>$D$4, F18>$D$4,F17>$D$4,F16>$D$4, F15>$D$4,F14>$D$4,F13>$D$4, F12>$D$4, F11>$D$4)))

The second trend would be if 14 cells in a row are seesawing up and down.
This formula would be
=AND(ISNUMBER(F19),AND(F19>F18,F18<F17,F17>F16, F16<F15, F15>F14, F14<F13, F13>F12, F12<F11, F11>F10, F10<F9, F9>F8, F8<F7, F7>F6))

Any way to simplify these formulas within the conditional format? What I really want to do is combine the two into one format (=OR(A,B) but it's too long. I have other conditional formatting to do. Any thoughts?

ChemistB

Originally Posted by duane

would you consider a macro that ran on anychanges to the workbook?

I might.

chemistb

the first condition is

and(isnumber(f19),or(countif(f19:f11,"<"&$d$4)=9,(countif(f19:f11,">"&$d$4)=9))

Thinking about the second condition!

regards

Dav

HI

In a general module enter the following functions

Please Login or Register  to view this content.

If your data is in the range F1:F200, and the mean is in D4 then your conditional formatting formula is:

=OR(inarow(F1:F200,D4),seesaw(F1:F200))

HTH

rylo

Thanks Duane and rylo,

I will try both these macros and let you know how things work out. I appreciate the help!

ChemistB