# Trend search

#### jpen

##### Active Member
Hi all,

I have a worksheet with in column D the yield of a process. Now I want to add a trend search. If 3 values in a row are having a lower yield then there should be an alarm (message box or color change of cell, etc.)

I want to do this without using macro's.

Any idea's

JH

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

##### MrExcel MVP
" If 3 values in a row are having a lower yield then there should be an alarm..."

Which of these would generate an alarm:

6;5;4;3
6;5;4;4
6;5;2;4

?

#### jpen

##### Active Member
The following trneds should give an alarm:

6;5;4;3 alarm on 4 and 3
6;5;4;4 alarm on first 4
6;5;2;4 alarm on 2

JH

#### jpen

##### Active Member
I have put in the next conditionel formatting. But the first empty cell at the end of the column is also red colored.

Formula = AND(B5<B4;B4<B3)

Any idea how to optimize the formula.

JH

#### jpen

##### Active Member
Some error in formula

Formula = AND( B5 < B4 ; B4 < B3 )

JH

#### jpen

##### Active Member
Add another formula to the conditional formatting

Rule 1 = A6="" action cell no fill
Rule 2 = AND(A6 < A5;A5 < A4) action cell fill red

JH

#### JustinWWolcott

##### Board Regular
For what its worth, here's my \$0.02;

Have your cell turn red when it drops below a rolling average.

In cell \$A\$1, put a 6 (it'll be a 6 cell rolling average)

then, for cell C7 set up the conditional formatting formula equal to this:
=C7<AVERAGE(OFFSET(C7,-\$A\$1,0,\$A\$1,1))

Replies
3
Views
890
Replies
0
Views
159
Replies
1
Views
355
Replies
7
Views
251
Replies
14
Views
4K

1,191,076
Messages
5,984,497
Members
439,893
Latest member
johnsboxftm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back