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.

[TABLE="width: 496"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]TREND[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD="align: center"]#VALUE![/TD]
[TD]<--array formula[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]Growing[/TD]
[TD]<--IF/AND formula[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]Growing[/TD]
[TD]<--Would expect to see "no trend" here[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]233[/TD]
[TD]125[/TD]
[TD]39[/TD]
[TD]8[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]27[/TD]
[TD]9[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]40[/TD]
[TD]316[/TD]
[TD]10[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]438[/TD]
[TD]464[/TD]
[TD]44[/TD]
[TD]11[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]14[/TD]
[TD]7[/TD]
[TD]12[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]489[/TD]
[TD]13[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]334[/TD]
[TD]182[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]196[/TD]
[TD]81[/TD]
[TD]17[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]16[/TD]
[TD]11[/TD]
[TD]18[/TD]
[TD]Growing[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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???