Excel: Find Missing Number In Sequence

Here's how to use MS Excel to find a missing number in a sequence.

The assumption is you have a column of numbers, say, Delivery Receipt Numbers, and that column is sorted in ascending order (from lowest to highest).

This Excel tip will make use of Conditional Formatting, so please hide your reading glasses because I won't ask you to read down a long column of numbers looking for the missing or out of sequence figures.

1. Highlight the second number all the way down to the last. So if your numbers are in cells A1 down to A100, please highlight cells A2 to A100.

2. Click on Format - Conditional Formatting

3. Click on the "Cell Value Is" dropdown list, then click on "Formula Is"

4. In the box to the right, type +(A2-A1)>1

5. Click on the "Format" button, the "Patterns" tab, and the background color that you like.

6. Click on the "OK" button.

7. Click on the "Add »" button.

8. Click on the "Cell Value Is" dropdown list, then click on "Formula Is"

9. In the box to the right, type +(A2-A3)<-1

10. Click on the "Format" button, the "Patterns" tab, and the background color that you like.

11. Click on the "OK" button. Click on the Conditional Formatting dialog box's "OK" button.

Test your column of numbers by typing in a number that is not in sequence. You'll notice that the numbers before and after the missing number will have a colored background. And that's how you can easily spot the missing Official Receipt or Delivery Receipt number in a sequence of consecutive numbers using your Excel spreadsheet.


Source: http://youtube.com/watch?v=jODw8EmSw54

[ First posted on 07/11/2007 by Manuel Viloria ]



Do you LIKE this page? Please let us know, and we will publish more of the content that YOU want. Salamat po!


"Yes, I Want To Grow My Financial Life & Get The 11 TrulyRichClub Blessings!"

To Grab This Offer, Click Option Below:

Truly Rich Club Philippine Residents Click Here

Truly Rich Club International Residents Click Here



Visit SupremeWealthAlliance.net ~ Kumita sa Internet, Kahit Super-BUSY Ka

Get In Touch With Manuel Today
Manuel@Viloria.net



  Previous Entry
  Next Entry


Secondthoughts @ Viloria.com
"We Host at H4P because H4P is affordable,reliable,
and customer-friendly!"
-- Manuel Viloria


Viloria.com
About Manuel Viloria
Secondthoughts
Archives
Angelhouser
Contact Us
Privacy Statement
RSS Feed


Google Reader


Copyright © 1996 - 2014 by Viloria.com All Rights Reserved.