Advanced Excel help - finding changes in a series of numbers

[Jabso]

I have thousands of lines similar to this:

Period	1	2	3	4	5	6	
Item 1	4	4	4	4	4	4	No
Item 2	4	4	4	5	5	5	No
Item 3	3	3	3	10	10	10	Yes
Item 4	4		4	4	4	4	No
Item 5	9	9	9	9	8	8	Yes

The last column are the intended results I want from the formula.

I want an if statement that shows significant changes which I define as:

- If the numbers INCREASE by MORE THAN 1 I want it to show yes.
- If the numbers DECREASE at all I want it to show yes.
- There are blanks. I want it to ignore those (i.e. don't treat as zero)
- If possible if in one period there is say 4 and then there is a blank period proceeded by a 6 I want it flagged as "YES".

The best I could come up with was a formula of nested if statements that showed if it has changed at all which wasn't 100% 'watertight'. It was something like if the average of those 6 (or less) numbers does not equal the first non blank number (hence the nested if statements) then mark it as "Yes". The reason why it wasn't watertight is there are occasions where the average may equal the first number but still have different numbers (for example 10, 5, 20, 5. The average is 10. It would be marked as "no"). Am I missing a simple formula I'm not familiar with?

 

[mdc]

I want an if statement that shows significant changes which I define as:

- If the numbers INCREASE by MORE THAN 1 I want it to show yes.
- If the numbers DECREASE at all I want it to show yes.
- There are blanks. I want it to ignore those (i.e. don't treat as zero)
- If possible if in one period there is say 4 and then there is a blank period proceeded by a 6 I want it flagged as "YES".

Jabsco, these are a bit badly defined. Specifically, would 2 3 2 2 2 2 be a YES (because the move from period 2 to 3 is negative) or a NO?

 

[Jabso]

Yes it should be yes. I want to catch out any thing that decreases and anything that increases by more than 1.

1 2 3 3 3 3 should be NO however. Also I need the blanks factored in.
4 BLANK 4 should be no.
4 BLANK 3 should be yes.


This has been suggested. =IF(OR(MIN(C2:G2-B2:F2)<0,MAX(C2:G2-B2:F2)>1),"Yes","No") However it only works if all 6 periods are not blank.

 

[mdc]

Yes it should be yes. I want to catch out any thing that decreases and anything that increases by more than 1.

1 2 3 3 3 3 should be NO however. Also I need the blanks factored in.
4 BLANK 4 should be no.
4 BLANK 3 should be yes.


This has been suggested. =IF(OR(MIN(C2:G2-B2:F2)<0,MAX(C2:G2-B2:F2)>1),"Yes","No") However it only works if all 6 periods are not blank.

Don't think this works, you'll have to type it out.

Are there any zeros other than the blanks?