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.
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({0;0;0;0;0;0;0;0;0;1})=–(A11<A10)
=1=–(A11<A10)
=1=–(TRUE)
=1=1
TRUE
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 }
Handy!
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