Need help with trend analysis

New Contributor

Hi All,

Need your support with the attached image of the data. I have a shortage report of an inventory, where I am looking at past 3 months data for some products and want to find out the trend.

I am looking to know if for a particular product what was the shortage trend.

For ex- In Jan it was 90%, Feb 50% and Mar 80% similarly there is no consistency some of them are showing  increasing trend, some are decreasing1.PNG and some are fluctuating.

Hence not able to build a combine logic for increasing decreasing and fluctuating 

1 Reply


Here is an example:


The formula in E2 is =SLOPE(A2:C2,{1,2,3})

It returns the slope of the linear trend line through the percentages in A2:C2.

A positive slope means an increase, a negative slope a decrease, and a slope near 0 means not much change.

The formula in D2 is =CORREL(A2:C2,{1,2,3})^2

It returns a measure of how well the data fit a linear regression. An R^2 of 85% or higher means that the slope provides a good fit, and an R^2 of 70% or less means that the slope doesn't really provide much information.

So in the screenshot, rows 2 and 5 show a clear increase or decrease, while rows 3 and 4 don't show a clear linear pattern.