# Worksheet Function to Test if Range Is Sorted

Did you ever need a worksheet function to determine if a range is sorted? Neither have I. But I’m all about answering questions that haven’t been asked.

`=SUMPRODUCT(--(A1:A10>=A2:A11))=--(A11<A10)`  The `SUMPRODUCT` part compares every cell in the range to the one below it. If any cell is greater than the one below it, it returns `TRUE` and the double unary converts that to a `1`. In this example, the `SUMPRODUCT` will return `1` – all cells are less than the one above it except A10 which is greater than A11 (blank).

That brings us to the right side of the equation. If A11 is less than A10 (as it is in this example), the expression will evaluation to `TRUE`. Again the double unary coerces the Boolean into a `1`. If A11 happened to be greater, it would return `FALSE`, or zero. We don’t really care if A11 is sorted, but we use the fact of whether it is to compare to the `SUMPRODUCT` result. If A11 is sorted, `SUMPRODUCT` will return zero for an otherwise sorted list. If not, `SUMPRODUCT` will return `1`. If anything else if not sorted, `SUMPRODUCT` will return a larger number and the whole expression will be false.

Up in the formula bar, I use Control+= (or F9) to evaluate portions of the formula

```=SUMPRODUCT(--({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}))=--(A11<a10 ) =SUMPRODUCT({0;0;0;0;0;0;0;0;0;1})=--(A11<A10) =1=--(A11<A10) =1=--(TRUE) =1=1 TRUE```

## 14 thoughts on “Worksheet Function to Test if Range Is Sorted”

1. Tushar Mehta says:

Any reason not to use the more obvious AND solution? Yeah, it’s an array formula but it’s soooo much more obvious. {grin}

Not to mention that it will work even with data on the last row of the worksheet (yeah, like that’s ever happened to me).

Suppose the data are in B2:B6. Then the *array*(1) formulas

=AND(B2:B5< =B3:B6) indicates an ascending order.
=AND(B2:B5>=B3:B6) indicates a descending order.
=OR(AND(B2:B5< =B3:B6),AND(B2:B5>=B3:B6)) indicates either an ascending or a descending order.

Remove the = to get strict order.

And with the named formulas below, the result adjusts to a changing data range.

aRng=OFFSET(Sheet1!\$B\$2,0,0,COUNTA(Sheet1!\$B:\$B),1)
aRng1=OFFSET(aRng,0,0,ROWS(aRng)-1,1)
aRng2=OFFSET(aRng,1,0,ROWS(aRng)-1,1)

=OR(AND(aRng1>=aRng2),AND(aRng1<=aRng2))

(1) For those who might be new to array formulas, to complete an array formula use the CTRL+SHIFT+ENTER key combination and not just the ENTER or TAB key. If done correctly, Excel will show the formula enclosed in curly brackets { and }

2. Rob van Gelder says:

Handy!

3. sam says:

Define
Lst = =’1′!\$A\$1:INDEX(‘1’!\$A:\$A,COUNTA(‘1’!\$A:\$A)-1)
LstOff =’1′!\$A\$2:INDEX(‘1’!\$A:\$A,COUNTA(‘1’!\$A:\$A))
Sort =AND(AND(Lst<=LstOff)AND(Lst>=LstOff))

Works for both Asc and Desc

4. sam says:
Sort=AND(AND(Lst<=LstOff)<>AND(Lst>=LstOff))
5. Ryan says:

Thanks for this! We were working with a large spreadsheet and wanted an easy way to conditionally format the column that was being sorted – and we had to do it without VBA or macros due to security settings. This was exactly what we needed.

6. micron1 says:

This is an awesome formula (and also works for text!). However, it doesn’t work when there is a duplicate number (or text) in the data. Is there a modification that might solve that? Thanks!

7. Dick Kusleika says:

This seems to work if there are duplicates.

`=SUMPRODUCT(--(A1:A10>A2:A11))=--(A11<A10)`

8. micron1 says:

That does it, thanks!!

Although now I’m seeing that it won’t work if there are blanks? Any ideas on that?

9. Dick Kusleika says:

This will work with blanks I think.

`=SUMPRODUCT(--(A1:A10<=A2:A11))=COUNT(A1:A10)-1`

10. micron1 says:

That latest one doesn’t seem to be working for blanks. I’m thinking this would require creating a temporary list in the array function that excludes blanks, no matter how many (for, say rows 1:10), and then compare that to the same logic applied to say, rows 2:11?

11. micron1 says:

When I say a temporary list, I mean creating those two lists “in the formula” without having to use a helper column, if that’s possible. Thanks again!

12. Dick Kusleika says:

It works for blanks unless the blank is the first or the last cell. I didn’t test the extremes, I guess. This seems to work for any blanks

`=SUMPRODUCT(--(A1:A10<=A2:A11))=COUNT(A1:A10)-1+ISBLANK(A10)+ISBLANK(A1)`

13. Dick Kusleika says:

Nope. That won’t work if the first two are blank.

14. Dick Kusleika says:

This might work for numbers, but not text

`{=INDEX(RANK.EQ(A1:A10,A1:A10,1),MAX((NOT(ISBLANK(A1:A10))*ROW(A1:A10))))=SUM((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))}`

Posting code? Use <pre> tags for VBA and <code> tags for inline.