Trending with Blanks

[ls_thomp]

I need to identify trends with a data set that contains blanks. I need to determine if each customer is trending up, down, or stable when there are months that they don't actually buy. These values are margin average per item by customer by month. Since these are averages of a margin per item, if the value was "0" it would affect the calculation so it has to be blank. I need the Trend column to define UP, DOWN, or STABLE.

If you can help, it would be appreciated.



Customer Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 Trend
Customer1 0.0117
Customer2 0.0058
Customer3 0.0125 0.0125 0.3000 0.0633 0.0125
Customer4 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100 0.0100
Customer5 0.0200 0.0213 0.0183 0.0261 0.0200 0.0231 0.0167 0.0183 0.0258 0.0223 0.0240 0.0195
Customer6 0.0048 0.0048 0.0108 0.0089 0.0089 0.0095
Customer7 0.0058
Customer8 0.0080 0.0020 0.0020 0.0022 0.0050 0.0020 0.0020
Customer9 0.0000
Customer10 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058 0.0058
Customer11 0.0900 0.0840
Customer12 0.2964 0.2862 0.2369 0.2600 0.2600 0.2250 0.3274 0.1500 0.2600 0.2600
Customer13 0.0643 0.0147 0.0195 0.0399 -0.0388 0.1500
Customer14 -0.0703 0.0317 0.0026 -0.0413 -0.0235 -0.0361 -0.0777 -0.0238 -0.0676 0.1052 0.1960 0.1147
Customer15 0.0200

[ls_thomp]

sorry, my browser or this website would not let me paste a picture of this.

[MrShorty]

If you can upload a sample file (Go advanced -> Manage Attachments), it will help us see how the blanks fit into the question. Even without considering how blanks figure into this, I cannot see how you intend to decide each case. Clearly, customer 4 and customer 10 are stable. Customer 3 (which goes up, then back down to the starting point) -- Up, down, or stable? Customer 5 is up and down around the same value -- is this stable? Customer 8, after an initial drop, has been pretty stable since -- down or stable? Customer 11 only has 2 entries that differ by only 7% -- is this down or stable? We need to understand how you are judging up/down/stable without blanks, before we can make suggestions for how that changes with blanks.

[ls_thomp]

Please let me know if the attachment posted. I did what you instructed to upload but the gui icon does not show where i can include it in the message.

[MrShorty]

The file uploaded fine. I put =ISBLANK(B2) into a cell, and it returned FALSE. Copied down and across, and it appears that all of the cells that look blank are not truly blank. I am guessing that these cells are from a formula that chooses to output empty string "" or some other non-printing character under certain conditions. Sometimes, it can be important to distinguish between "blank" that is truly blank (as in the ISBLANK() function will return TRUE) and "blank" meaning some kind of invisible text string.

I still don't understand how you are deciding when a sequence is up/down/stable, so I cannot make any recommendations. Perhaps you can add some expected results and describe how you came up with those results?

[ls_thomp]

yes there are formulas that are looking up values. The formulas are an averageifs and it returns blanks if there are no sales data. I have several customers who order every other month (which is why some items are blank, some that order every day so there is monthly data. I am trying to identify customers that have a margin per item are trending up, trending down, or are stable. Obviously, it is a difficult thing if customer order once a year and there is one data point, however if there are two or more data points, it should be able to create a regression line to measure trends.

[MrShorty]

So a simple regression will be enough to determine up/down/stable? The SLOPE() function ignores blanks/text/boolean (see helpfile here: https://support.office.com/en-us/art...a-61d7e01276b9 ), so my first thought would be to simply use the SLOPE() function SLOPE(B2:Q2,$B$1:$Q$1) to get the slope. If this value is "negative" then the trend is down, if "positive" then trend is up, if "zero" then trend is stable. An IFERROR() could be used to trap the Div/0 error for the single entry rows. You would need to consider how close to 0 the slope gets before you decide something is truly 0, as floating point error and other noise in the data may cause some that you think should be 0 to return a non-zero result. I would suggest that you start with a SLOPE() function and fine tune it from there.

[ls_thomp]

I used =IFERROR(SLOPE(C14:N14,C14:N14),"") and it only returns 1.000 and blanks when there is one value. I can add the up, down, stable logic if it worked.

[FlameRetired]

Would sparklines help?

In the attached is the SLOPE MrShorty suggests. I first cleaned up the "blanks" and made them blanks. This leaves broken sparklines in column S which I believe give visual confirmation. I made no attempts to remedy floating point errors.

Please Login or Register  to view this content.

[MrShorty]

Is there a reason you have your y_values and x_values arguments the same? It is trivially true that y=1*y -- you wouldn't need a regression function to know that. I would expect those two ranges to be different ranges. Note in the example I used, based on your sample file, the first argument is to the y values in row 2, and the second argument is to the dates in row 1. (Also note the mix of relative and absolute references so that, when I copy this down, the y_values argument moves down as I copy it, but the x_values argument stays fixed to the dates in row 1.)