Sum cells if a word is repeated in sequence without more than 2 interuptions.

[bermandl]

Dear All,

I am trying to identify trends and I have a row that lists whether the price of a today's commodity is lower than yesterdays. This row is obviously filled with Negative, Positive, and Unchanged.

I am trying to find a formula that can count how many negatives i have had only if the sequence isn't interrupted by 2 sequential positives.

So i was hoping i could get a result saying "Trend Negative Day 5" for instance.

Please Help!!!

David

[JBeaucaire]

In C7, put the number 1

In D7, put this formula, then copy to the right:

=IF(D5=C5, "Trend " & D5 & " Day " & RIGHT(SUBSTITUTE(C7," ", " "),3)+1, D5 & " Day 1")

[bermandl]

Dear JBeaucaire,

I tried followed your instructions, however, after day 2 I get #VALUE despite the fact that it's still positive. Any ideas?

I really appreciate your help with this.

Best,

DLB

[JBeaucaire]

Works for me. An excerpt of your sheet...

[bermandl]

It Works! Thank you.

Now if you can bear with me for just a bit longer, I was wondering if the formula can be tweaked for two exceptions.

1. If the status of the price is unchanged, can we tweak the formula to continue along the same trend?

2. If say, a positive trend is interrupted by only 2 negative days, can the formula be tweaked to continue along the same original (positive) trend?

These two exception are extremely important since, as you probably know, a trend can have little exceptions every now and again despite going in the same direction.

I seriously cannot thank you enough for this, I am much obliged.

David

[JBeaucaire]

Update your sheet (or my excerpt) and fully demonstrate your desired results.

[bermandl]

As requested. Thanks again for your help guru.

[JBeaucaire]

I think I consider a trend change differently than you do. This formula gives much of the same results you've outlined, but not completely.

In C10 manually insert the first value: Positive Trend Day 1
In D10, enter this array formula:

=IF(OR(D5="UNCHANGED",OR(TRIM(LEFT(C10,FIND(" ",C10)))=(D$5:G$5))),LEFT(C10,FIND(" ",C10))&"Trend Day "&RIGHT(SUBSTITUTE(C10," "," "),3)+1, D5&" Trend Day 1")
...confirmed by pressing CTRL-SHIFT-ENTER to activate the { } array.

Now copy to the right until you get 3 cells from the end, where you'll have to stop.

The logic here is:

If row 5 has the same value as the row10 prior cell,or if it says "unchanged", it continues the current list.

If it is the opposite, it checks the next 3 cells in row 5, too, to see if even one has the current value, if so, it continues the current trend.

If not, then it immediately starts the new trend. So, my new trends seem to start sooner than yours as a result. If positive switches to negative and stays negative/unchanged for 4 cells, then the negative trend actually started at the first cell.

See BD10 to see what I mean. You didn't admit a negative trend until BK10, which I consider incorrect.

You could change the D5:G5 in the first formula to shorten or expand the "check" range. Just remember to confirm your changes with CTRL-SHIFT-ENTER to reactivate the array before you copy the new formula over.

[bermandl]

You are brilliant. This is EXACTLY what I was looking for, and I am confident we can put a nice big "SOLVED" next to this one.

Thank you a million times over.

DLB

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.