Results 1 to 8 of 8
Thread: Formula to check numbers (xp)

20031125, 22:11 #1
 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 a1n1, 115) 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

20031125, 22:25 #2
 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)

20031125, 23:41 #3
 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 itself1. 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>

20031125, 23:41 #4
 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

20031126, 00:10 #5
 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

20031126, 04:54 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,198
 Thanks
 2
 Thanked 460 Times in 379 Posts
Re: Formula to check numbers (xp)
<P ID="edit" class=small>(Edited by macropod on 26Nov03 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 firstmentioned 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20031126, 19:53 #7
 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

20031126, 20:12 #8
 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