Thread: Conditional Formatting Formula for zig zagging data

Hi All,

I"m looking to flag data if the last 14 data points in a row (column actually) have been following an up and down pattern. I only need the last cell in the series to be flagged. So if, A14>A13, A13<A12, A12>A11, A11<A10....A1, then A14 will turn red. Also would need to work for the inverse (A14<A13, A13> A12...). Any ideas on how to compress this in my conditional formatting? I was trying to work with COUNTIF's but couldn't get it to work.

Thanks in advance

ChemistB

You could use this formula in conditional formatting:

=AND(A14>A13,A13<A12,A12>A11,A11<A10,A10>A9,A9<A8,A8>A7,A7<A6,A6>A5,A5<A4,A4>A3,A3<A2,A2>A1)=FALSE

Condition = red

HTH

Jason

Thanks Jason,

Any way of compressing that? That would only be half the formula as I would need an OR(Condition1, Condition2) where condition1 is what you wrote out and Condition2 would be the AND statemen beginning with A14<A13. I have to do this on a lot of spreadsheets and want to make it compact if possible.

Thanks again for the quick response.

ChemistB

Try this formula in conditional formatting:

=ZIGZAG(A1:A14)=FALSE

Just kidding. Sorry, I can't think of any way off the top of my head to compress that.

Jason

Hmmm, maybe I do need to create a user function or write a macro for it. Oh well, I will continue to ponder this one. Thanks for your help, Jason.

ChemistB