# Thread: Formula to check numbers (xp)

1. ## 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. ## 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. ## 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

4. ## Re: Formula to check numbers (xp)

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

5. ## 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. ## 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

7. ## 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. ## 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
•