How can I make Excel tell me when a number is out of sequence?

November 20, 2008 6:46 PM

How can I make Excel tell me when a number is out of sequence?

I have about 1700 rows of numbers in one column in Excel. I need to know if any of the numbers are out of sequence or equal to each other, and if so, which rows. I am almost a complete Excel novice so please be gentle with me! Thanks in advance.

posted by Beautiful Downtown Burbank to computers & internet (6 comments total)

Sequential: =IF(A2=A1+1,"good","bad")
Equal to prev: =IF(A2-A3=0,"equal","not equal")
posted by b33j at 6:51 PM on November 20, 2008

I should say, stick those formulae into a column, and make the A1, A2 references applicable to the column your numbers are in. Once you have one formula in place, grab the black handle on bottom right of cell and drag to replicate the formula through that column. Use the next column for the other formula.
posted by b33j at 6:53 PM on November 20, 2008

Excellent, thank you so much!
posted by Beautiful Downtown Burbank at 7:04 PM on November 20, 2008

To add to b33j, if it's a lot of information,consider conditional formatting to set "bad" to have a yellow background. It helps to much more quickly eyeball the out of sequence data.
posted by toaster at 7:07 PM on November 20, 2008

It's not quite as good as the response posted, but you could also create a chart of the values & that should tell you of, at least, any gross errors.

Another alternative is copy / paste the original in the left column of a diff program*.

Sort the column & then copy / paste the results in the right column.

* free online diff program http://www.quickdiff.com/ A diff program tells you if anything's different between two files - so if you have one that you know is sorted, if you can quickly find out if the other is also sorted.
posted by Muffy at 8:40 PM on November 20, 2008

I always handle this by using Conditional Formatting.
Highlighting cell A2, I set up the condition:
"Formula is" "= A2 > A1"
and then pick a format of bright yellow background & red bold text.

The out-of-order elements pop out at you, no matter how fast you page by, nor how small the cells are zoomed.
posted by IAmBroom at 9:37 PM on November 20, 2008