Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Let me try this again

  1. #1
    Guest

    Default

    I did a poor job of explaining my issue on a previous post, so let me try again...

    - I've got a series of data in 9 columns.
    - Any or all of the columns could be empty
    - If any cells have data in them, the data will be a numberic value (could be positive or negative)
    - Any data in the cells must be greater than any values in an cells to the left of them.

    Examples:
    This data would be OK.
    A1=1
    B1=2
    C1=3
    D1=4
    E1=5
    F1=6
    G1=7
    H1=8
    I1=9

    This data would also be OK (note that I've used "null" to indicate an empty cell
    A1=1
    B1=null
    C1=null
    D1=null
    E1=null
    F1=2
    G1=null
    H1=null
    I1=9

    This data would not be OK because the data in A1 is greater than the data in F1.
    A1=1
    B1=null
    C1=null
    D1=null
    E1=null
    F1=0
    G1=6
    H1=9
    I1=11

    This data would not be OK because the data in B1 equals A1 (it needs to be greater than, not greater than or equal to).

    A1=1
    B1=1
    C1=3
    D1=4
    E1=5
    F1=6
    G1=7
    H1=8
    I1=9

    Any help would be appreciated.

    Scot Jonas
    jonas.sb@pg.com

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can use Data validation if the data is being entered from the keyboard

    Use the custom option. For example,for column D, the formula might be =D2>MAX(A2:C2). By using relative cell references, you can copy the validation down the whole column.

    [ This Message was edited by: lenze on 2002-03-06 12:46 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-06 12:18, Anonymous wrote:
    I did a poor job of explaining my issue on a previous post, so let me try again...

    - I've got a series of data in 9 columns.
    - Any or all of the columns could be empty
    - If any cells have data in them, the data will be a numberic value (could be positive or negative)
    - Any data in the cells must be greater than any values in an cells to the left of them.

    Examples:
    This data would be OK.
    A1=1
    B1=2
    C1=3
    D1=4
    E1=5
    F1=6
    G1=7
    H1=8
    I1=9

    This data would also be OK (note that I've used "null" to indicate an empty cell
    A1=1
    B1=null
    C1=null
    D1=null
    E1=null
    F1=2
    G1=null
    H1=null
    I1=9

    This data would not be OK because the data in A1 is greater than the data in F1.
    A1=1
    B1=null
    C1=null
    D1=null
    E1=null
    F1=0
    G1=6
    H1=9
    I1=11

    This data would not be OK because the data in B1 equals A1 (it needs to be greater than, not greater than or equal to).

    A1=1
    B1=1
    C1=3
    D1=4
    E1=5
    F1=6
    G1=7
    H1=8
    I1=9

    Any help would be appreciated.

    Scot Jonas
    jonas.sb@pg.com
    Scot,

    What follows will enable you to check whether a given set of numeric values are in ascending order without equality:

    Array-enter:

    =COUNT(A1:H1)=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(A1:H1,A1:H1)),MATCH(A1:H1,A1:H1),""),IF(ISNUMBER(MATCH(A1:H1,A1:H1)),MATCH(A1:H1,A1:H1),""))>0,1))

    where A1:H1 houses the set of the values of interest.

    In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

    Note. I hope you're not going to copy this formula to a huge number of rows, otherwise it will have an adverse effect on the performance of your spreadsheet.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-06 13:56 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Forgat to tell you that the array-formula returns TRUE if the set of values is OK, otherwise FALSE.

    [ This Message was edited by: Aladin Akyurek on 2002-03-06 13:56 ]

    [ This Message was edited by: Aladin Akyurek on 2002-03-06 14:11 ]

  5. #5
    Guest

    Default

    Aladin,

    THANKS!!!!!

    Scot

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •