How to find missing numbers in a sequence

Question

Level 1

0 points

How to find missing numbers in a sequence

I am having a hard time figuring out how to use conditional formatting to identify where numbers are missing in a sequence. For example, if I have a really long list of numbers, how can I identify if there are any missing from the sequence.

like:

101

102

104

105

107

The numbers 103 and 106 are missing, but how can apply conditional formatting to identify this for me without having to do it manually?

In Excel I apply conditional formatting with this formula =(A4-A3)>1 (I select only A2 down to be formatted)

Any help would be greatly appreciated!

Numbers-OTHER

Posted on Nov 26, 2011 12:09 AM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Nov 26, 2011 5:06 AM in response to msgoyer

You can add a new column and insert a formulathat checks to see if the current

value is the next value i the sequence relative to the previous value:

C5=IF(B5=(B4+1), "OK", "MISSING")

select C5 and fill down

Reply

Answer 2

msgoyer Author

Level 1

0 points

Nov 26, 2011 1:26 PM in response to Wayne Contello

Thanks so much for responding! Unfortunately, I need a reallllly basic breakdown of how to do this step by step. When I add a column and include "OK," and "missing" for 4 numbers in a row and then try to insert that formula into the conditional formatting, I can't - it keeps making it say "table 1: A" etc... any ideas? Or, could you kindly just break it down for me step by step? I would REALLY appreciate it 🙂

Reply

Answer 3

Nov 26, 2011 5:09 PM in response to msgoyer

Madam Goyer,

If you wish to control the format with an equation, the conditional formatting must be applied to the column of equations, not the data. You may use exactly the formula you suggested and if you Conditionally format for the condition TRUE, you can display a distinctive fill on the row just above mission data.

Here's an example:

What I have done is set the normal text color to a very faint gray so you can see the formula result, but you could just as well have set the text color saturation to zero to make the calculation result invisible.

Regards,

Jerry

Reply

Answer 4

msgoyer Author

Level 1

0 points

Nov 26, 2011 9:00 PM in response to Jerrold Green1

Yay! I got it, thank you so much!

Reply