View original (French)

How to notice a jump in numbers on an ordered Excel table

Solved
romi -  
Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   -
Hello,

I have an Excel table whose numbers are already sorted. how can I notice a jump in a number?

Configuration: Windows / Chrome 50.0.2661.102

3 answers

  1.
    Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    "how to notice a digit jump?"
    Can you explain what your question means?
    For us, it's nonsense, because there is no "digit jump",
    neither in computer science nor in mathematics.

    --
    Well done, retirement! Especially in the Caribbean... :- )
    Raymond (INSA, AFPA, CF/R)
    0
    1. romi
       
      Thank you for the clarification. Indeed I exported the data from a database to Excel. And along the row direction the numbers start at 1 and go to an infinite number, for example 1,2,3,4,5,6,7, etc. How to notice a gap, that is 1,2,4,5,6, etc. How to know that 3 does not exist in the column. Thanks
      0
  2. ccm81 Posted messages 11033 Status Member 2 434
     
    Hello to you both

    Try using a conditional formatting rule to color cells that precede a "gap"
    If the range to check is A2:Axx you select it
    then you give the formula for the CF
    =($A2<>$A3-1) then format ...

    Cdlmnt
    0
    1. romi
       
      hello ccm81,
      i’m trying with your recommended formula but nothing concrete. i don’t understand MFC expressions, format, and how to apply the formula.
      thanks
      0
    2. ccm81 Posted messages 11033 Status Member 2 434
       
      You can do without an MFC by adding a column as via55 advises, I greet you warmly
      Your data in column A starting from row 2
      in B3 you type the formula
      =IF(A3=A2+1,"","x")
      to fill down

      http://www.cjoint.com/c/FFdpYMHRaOe

      Cdlmnt
      0
      1. romi > ccm81 Posted messages 11033 Status Member
         
        Hello ccm81,
        I just applied the recommended formula and it’s great. thank you very sincerely.
        very cordially,
        0
  3. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello to both of you

    Assuming the numbers in column A and that there is only a single number jump
    =FACT(MAX(A:A))/PRODUCT(A:A) will return 1 when nothing is jumped, the jumped number otherwise

    (if there are several jumps the formula returns the product of the jumped numbers)

    Best regards
    Via

    "I imagination is more important than knowledge." A. Einstein
    0
    1. romi
       
      thank you VIA55,
      I find a solution to my query when I apply the recommended formula with a column of numbers starting at 1. but the problem is that when I try it in a table that starts at 6450 and ends at 6600 and maybe 6501 is skipped, the formula does not work to recover the number 6501 that was skipped.
      0
      1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482 > romi
         
        It is you who stated "
        “the numbers start from 
        1
        to an infinite number, for example 1,2,3,4,5,6,7 etc...”!"
        0