Detect If A Range Of Numbers Is Descending Downwards

[Genus Max]

Hi, I'm trying to find out how to detect if a range of numbers is descending downwards, ie 10,9,8,7,6,5,4,3,2,1.

Are there any functions builtin to detect downward trends in a range of numbers, im not sure where to look, so all help appreciated.

Thanks, much appreciated.

[MrShorty]

I expect it really depends on how you intend to define "descending downwards".

I could see something as simple as =(A10-A1)<0 would return TRUE if the last number is smaller than the first number. Any data set where the last number is smaller than the first number would be considered "descending downwards".

Or I could see something as complex as a full "slope test" (https://stattrek.com/regression/slope-test.aspx ) where I go through a full statistical test where I calculate slope of the line and other statistics and do the full hypothesis test "is the slope significantly less than 0".

And, of course, other possibilities with varying levels of complexity. It all depends on how you intend to decide what it means for the data to descend downwards.

[Genus Max]

Hi, I just need to detect a basic downward trend of numbers for now ie 5,4,3,2,1.

I was thinking of using countif, was wondering if there were functions builtin specifically for detecting something as simple as 5,4,3,2,1.

Thanks.

[MrShorty]

To my knowledge, there is not a simple, plug and play built in function that detects "descending downwards". My first proposal [=(A10-A1)<0 or =(A5-A1)<0] would return TRUE for both sequences. Would that simple test be adequate for you, or do you need something more complex to account complexities in other data sets?

[Gregb11]

Maybe you could try this. Assuming your values are in Cells A1:J1, In cell A2 you could enter:
=TREND(A1:J1)
Then in cell A3 try:
=IFS(B2>A2,"Upward",B2<A2,"Downward",TRUE,"flat")

This should match the "trend line" if you were to graph the points.

[LJMetzger]

I vote for an auxiliary column that contains the difference between each set of two rows.
Use Countif on the 'auxiliary column' to determine if all the differences are positive.
See the attached sample file.

Please Login or Register  to view this content.

Lewis

[hrlngrv]

Bounds decreasing: =A1>A10

Generally decreasing: =SLOPE(A1:A10,ROW(A1:A10))<0

Monotonically decreasing: =AND(A1:A9>A2:A10)