Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to check numbers (xp)

    In cells A1 to J1 the user can enter the numbers 1 to 10 in any order (ie a1=9, b1=5, c1=7 etc)
    They should not miss or repeat any numbers (so each number entered once and once only)

    I need a formula(s) (or conditional format) that will check that this is the case. The range may grow in the future (ie a1-n1, 1-15) so it would be good to keep it dynamic

    Any ideas?

    My best guess so far is to compare the sum of the values entered with the sum of 10+9+8+7 etc -which would capture most mistakes - Not sure how to write a function that will calculate 10+9+8 etc.

    Can use vba if required but their must be a formula method (ever hopeful)

    thanks

    Simon

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to check numbers (xp)

    Ok the formula to add 1 to n together is:

    (1+n)*(n/2) (all credit to http://mathforum.org/library/drmath/view/57919.html)

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formula to check numbers (xp)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> forrests

    OK I am not sure the sum is a great way to make sure numbers are unique. Here is why:

    If I enter 1,2,3 the sum is 6

    If I enter 1,1,2,2 the sum is 6. Here is but one example, thing about adding 10 1s for a 10, or 2 4s for an 8... <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    I think you need to find a way to limit the user's choice. What I am thinking of is data validation with some rules.

    If the user enters 1 in cell A1, then all remaining cells can't have 1 any more, so the dropdown list will lose the 1.

    Same thing for 2. Once the user enters it via the drop down, then it should not be a choice any more.

    I have not used Data Validation this way, but VBA is very easy to do this. You simply have the code eliminate the chosen number for the first cell, from all the dropdowns remaining.

    This can be done using the Index of the dropdown List, which should be the number itself-1. Remember Dropdowns are Zero based, so the first item has index = 0. If the list does not have any item selected, its index is -1.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to check numbers (xp)

    =AND(SUMPRODUCT((A1:J1<>"")/COUNTIF(A1:J1,A1:J1&""))=10,COUNT(A1:J1)=10)
    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to check numbers (xp)

    Check out this site.

    It shows how to setup a datavalidation list that is dynamic and eliminates items from the list as they are chosen.

    Steve

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula to check numbers (xp)

    <P ID="edit" class=small>(Edited by macropod on 26-Nov-03 14:54. Additional material for 'dynamic' updating added)</P>Hi Simon,

    For the example you gave, you could use a conditional format such as:
    =(OR((A1)>COUNT($A1:$J1),A1<1))
    to test whether the number entered is within the acceptable range, and a second conditional format such as:
    =(COUNTIF($A1:A1,A1)<>1)
    to test whether any number has been duplicated.

    If you were going to change the number of columns into which data could be entered, you'd nee to change the $J1 in the first conditional format formula to suit.

    Alternatively, define a name for J1 and use:
    =(OR((A1)>COLUMN(Test),A1<1))
    as the first conditional format formula. Then you can add/delete columns between A & J and the conditional format formula will update dynamically. This also has an advantage over the first-mentioned version in that you don't need to have all the values entered before the condtional format testing takes full effect.

    Set up the conditional formats in A1, then copy across as far as you need.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to check numbers (xp)

    Thanks for all your help guys.

    The Contextures.com/xldataval03.html provided what I needed, but I can also use Aladin's suggestion

    Simon

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to check numbers (xp)

    Another method (without using the contextures "variable list") is to put this into data validation (select A1:J1, data -validation -custom- formula):
    =AND(A1=INT(A1),COUNTIF($A$1:$J$1,A1)=1,A1>=1,A1=< 10)

    It ensures:
    1) integer values
    2) unique entries
    3)>=1
    4)<=10

    If you enlarge the range, just select the new range, and edit to include all the items and change the <=10 to < =whatever.

    Steve

Posting Permissions

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