formula to recognize increasing trends

[lar9149]

Hi all,

I wanted to create a formula where I can have excel recognize 2 increasing trends that follow each other within a group of numbers. And also where the formula can compare the largest number in the first increasing trend to the largest in the 2nd, along with the 2nd largest in the first trend vs the 2nd largest in the second trend and so on...the trend should consists of at least 3 numbers. I have included an attachment for an example.

[MrShorty]

This looks like the same problem you posted last week and got no response on.
It feels to me like a "signal processing" kind of problem. I am not very good with these algorithms, but here are some elements that I see in your data:

1) You need to carefully describe to Excel (and to us, if you want our help programming this into Excel) how it should recognize a an increasing trend. I notice that your bolded "increasing trend 1" does not increase continuously, since the third entry is the same as the first and less than the 2nd. Spend some time with your data and identify exactly what criteria you want Excel to use to identify an increasing trend.
2) I find that most of the time when I tackle a problem like this, I almost always end up with a column that calculates "slope" of some kind. This might be =(y2-y1)/(x2-x1), or some other calculation of slope (depending a lot on how much "smoothing" my signal data needs),
3) Since your focus is locating increasing trends (your old thread also mentioned wanting to find decreasing trends as well), the second calculation I would probably include is a SIGN((slope) function, so that I have a column of -1,0,1. This column might be a good column to look at when looking for the criteria for identifying an increasing/decreasing trend.
4) I expect that, once you have the trends located, the comparison step will be relatively easy (I would expect a lookup function, but it might depend on exactly how you/we approach identifying the trends).

[AliGW]

Please explain the first (bold) 'upward' trend - are the numbers in the sample file correct? It does not look like and increasing trend to me with 1.1 between 1.2 and 1.6.

[lar9149]

Thank you for MrShorty and AliGW for answering my post. You are correct MrShorty I posted a previous trend, but thought maybe I put too much information in the post. For now, I want to focus on the increasing trends and perhaps deal with the other trends later to make the topic more focus.

As far as your questions about the bolded increasing trend, yes the 1.1 (third entry) is out of sequence. However, I would like excel to exclude this value. As long as the trend forms an approximate upward trend that includes at least 3 numbers in approximate sequence . I have included other examples in the below attached file for more detail. Sample 1 shows where the out of sequence number is excluded. Sample 2 shows where if row 20 was replaced with 1.0, the 1st trend would not be increasing but rather decreasing and, thus, not counted as 2 increasing trends. Sample 4 shows an increasing trend, but where the numbers could be repeating (like the bolded 1.2). Hope that clarifies the issue a bit more.

[MrShorty]

As long as the trend forms an approximate upward trend that includes at least 3 numbers in approximate sequence

You and I can intuitively work with this kind of "fuzzy" logic, but computers, for the most part, does not inherently know how to deal with fuzzy logic like this. It seems to me that a lot of this kind of "signal analysis" is translating this fuzzy logic into something concrete that we can program into the computer.

As I indicated in my previous post, I find that I almost always include a "slope" calculation of some kind in this sort of thing. I have included a simple change calculation in this spreadsheet to illustrate how this might look. I have also included a test for start trend to see how it works, and this particular test seems to capture all of the examples you have given of increasing trends.

An additional consideration that might be worth looking at -- does an increasing trend need to change by a minimal amount? For example, would .011, .011, .012, .012 be considered an increasing trend? The test I have included will flag this as an increasing trend. I notice that all of your examples show a trend that increases 0.005 (.011 to .016 or .012 to .018). This might also be a part of what you mean by "approximate increasing trend".

And on and on we go, trying to define exactly what "approximate increasing trend including 3 numbers" really means to you. I hope I have given you a starting point to see what you need to look at to create the criteria that are needed to come up with a series of calculations and logical tests that will go into identifying these trends. Let us know how we can help further.

[lar9149]

yes MrShorty, it was helpful as a starting point...I was able to develop a method to have excel identify "approximate increasing trends" that follow each other. I have attached the work I have done so far. As you see in row 7, I created the formula to get rid of repeating numbers (e.g. row 15, 16 column 6 0.8, 0.8 follow each other). The rest are the formulas you gave me, except row 13 where I used the true,false patterns to label the increasing trends. I wonder if you can give me suggestions on how to have the numbers compare to each other in the trends?

[MrShorty]

As I indicated above, I would expect the comparison step to be a lookup function. Are you familiar with Excel's lookup functions? https://support.office.com/en-us/art...1-63f26a86c0eb

I apologize for not reverse engineering the logic in column M. There is a lot there, I assume you understand all the logic you are using to determine i1,i2, etc. The next step I would use would be to create a unique identifier for each point within those trends. A formula (probably in column N) that will look at column M and note that we are in i1, then identify whether this point is part of the increasing trend, and label it as point 1 within i1, or point 2 within i2 or point 4 within i2, and so on. Then combine that information into a single, unique identifier for each desired point (i12, i13, i21, i22, i23,...). Then, in the part of the spreadsheet where I want to make the comparison, I can put i11 into a cell, i21 into an adjacent cell, then perform the lookup [probably something like =INDEX($G$8:$G$43,MATCH("i12",$O$8:$O$43,0))].

[lar9149]

I started to label the trends as you mentioned i11, i22...I had the raw data converted to round numbers in column g. It is relatively easy to find i11 as mentioned so the formula in column almost always gives true in the start of the increasing true. However, I gave a screenshot of an issue...highlighted in yellow. This number (0.8) isn't increasing so I don't want to count this as a point of the "approximate increasing trend" i1. Is there a formula that can say like if n8="i1x" and n9>n8, result i1(x+1)? So if n8="i11" and g9>g8, the formula will result in i12. And likewise for the next column.
screenshot 101616.png

[MrShorty]

As far as the Excel syntax goes, it is the same kind of IF(AND(...),value_if_true,value_if_false) function, like you are using. It's not clear to me where you are storing you "x" or "x+1" values (unless your intention is to try to "store" them in a text string, which is something I try to avoid). Something like =IF(AND(N8=x,N9>N8),x+1,x) would keep track of x, then have a column next to it that concatenates "i1"&x.
Along the same lines, you probably want to keep track of the trend number outside of the text string as well. So, if I were doing it:

M5 =0
M6 =IF(test for start of new trend,M5+1,M5) Column M keeps track of trend number
N5=0
N6=IF(test if this is part of increasing trend,IF(test for next point,N5+1,N5),0) Column to keep track of point number.
O6=CONCATENATE("i",M6,N6) perhaps nested in and IF() function if I want it to be blank when not part of an increasing trend.

Because I would want to keep track of trend number and point number as numbers, I find this is easier than trying to put it all into a text string in a single formula, then pull the prats out of the text string when they need to be changed.

[lar9149]

Thank you for your help, it worked and appears to be all done, attached is a copy. I had to modify some suggestion because I only wanted to compare increasing trends that follow each other, unlike the very first trend, which is followed by an approximate decreasing trend.