Excel formula to check and see if a column is in ascending order

Question

I need a formula to check and see the the values in a given column are in ascending order or not.

True Example:
4
29
54
79
107
131
156
177

False Example:
4
29
104
79
107
186
156
177

Any ideas?

Answer 1

You can use an array formula to accomplish this.

Let's assume you have data in B4:B10.

In whichever cell you want the TRUE or FALSE to appear, which will indicate whether a column is sorted by ascending or descending, place the following formula in the cell:

=AND(B4:B9<=B5:B10)

Instead of pressing enter like normal, press CTRL+SHIFT+ENTER.

You will know if it is an array formula because you will see curly braces around it, such as:

What you are doing is, say you have a list of n values, you are evaluating the first value to the n-1 value and checking to see if they are in order, if they are all in order then TRUE is returned, if any are out of order then FALSE is returned.