Populate text when 4 cells consecutively increase

[pardesi6]

Hi there -

Haven't been able to find an answer to this exact question, and am not able to get nestedIFs to work. In short, I'm doing a trend analysis based on ranks that looks like the below:

Jan | Feb | Mar | Apr | TREND
2 | 5 | 6 | 8 | GROWING
3 | 1 | 7 | 3 | NO TREND
4 | 2 | 8 | 7 | NO TREND

I want excel to populate this "Trend" column when it sees 4 months with consecutive increases. Thoughts?

Thanks in advance for your help!

 

[FryGirl]

Maybe =IF(AND(B1 > A1,C1 > B1,D1 > C1),"Growing","No Trend")

 

[cooper645]

=IF(AND(A2<B2,B2<C2,C2<D2),"Growing","No Trend")

how does that work for you?

 

[cooper645]

=IF(AND(A2 < B2,B2 < C2,C2 < D2),"Growing","No Trend")<b2,b2<c2,c2<d2),"growing","no trend")<="" html=""></b2,b2<c2,c2<d2),"growing","no>

 

[FryGirl]

cooper645,

You might need to place spaces between your ">" or "<" signs.

 

[cooper645]

cooper645,

You might need to place spaces between your ">" or "<" signs.

figured after attempt 2,

but thanks

 

[CodeNinja]

If you are not wanting to hard code the 4 columns, you could use:

{=IF(SMALL(A2:D2,COLUMN(A2:D2))=A2:D2,"Growing", "No Trend")}
This is an array formula, and will need to be entered using CTRL + SHIFT + ENTER.

The advantage is, you can dynamically change D2 to any other column... so if you want to know that the last 7 columns are incrementing... it will work...

Good luck,

CN.

 

[MarcelBeug]

Similarly, with a regular formula:

=IF(3=SOMPRODUCT(--(B2:D2>A2:C2)),"Growing","No Trend")

 

[pardesi6]

Thanks, guys. The if/and formula is only semi-working as it's not catching rows that don't have a trend (not sure why - maybe it's because some columns are blank?). the array formula is giving me an error (first row).

Here's a snapshot of real data. It's hardcoded #s with the blanks having nothing in them.

May	June	July	August	TREND
2	1	1	1	#VALUE!	<--array formula
4	2	2	2	Growing	<--IF/AND formula
5	3	3	3	Growing
29	15	8	4	Growing
57	18	4	5	Growing
		6	6	Growing	<--Would expect to see "no trend" here
9	5	9	7	Growing
233	125	39	8	Growing
		27	9	Growing
	40	316	10	Growing
438	464	44	11	Growing
17	14	7	12	Growing
		489	13	Growing
			14	Growing
334	182		15	Growing
			16	Growing
	196	81	17	Growing
18	16	11	18	Growing

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[MarcelBeug]

Ah, new information - blank cells:

=IF(AND(3=SOMPRODUCT(--(B2:D2>A2:C2)),COUNT(A2:D2)=4),"Growing","No Trend")

Edit: I see a lot of unexplainable "Growing" in your example data???