Results 1 to 12 of 12

20040503, 14:15 #1
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
Validation formula or function? (Excel2000)
Hi All
I need to put either a formula or function to check whether an adjacent cell contains a valid fourdigit numeric integer value in the range 1000 to 9999 inclusive. (It represents a telephone extension)
I have around 1500 such entries in a data column.
Is it more efficient to define a custom function for this, e.g. =ISVALIDphoneExt( )
or is it better to simply use an Excel formula???
I am interested in reducing file size and calculation speed.
I have another 100 plus such more complex validation check formulas to define so it would be interesting to hear from others as to the pros and cons of using custom functions.
I want the validation 'formulas' to be 'real time' i.e. I don't want a VBA routine that loops through data columns checking for valid contents  I want red backgrounds as soon as an invalid entry is entered!
zeddy

20040503, 14:22 #2
 Join Date
 Jan 2002
 Location
 Missouri, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Validation formula or function? (Excel2000)
Excel's builtin formulas tend to be faster, so might consider that.

20040503, 14:22 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Validation formula or function? (Excel2000)
=and(isnumber(a1), a1>=1000, a1<=9999)
Formulas are much much faster than User defined functions (UDFs), even megaformulas
Steve

20040503, 14:26 #4
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Validation formula or function? (Excel2000)
As an alternative to using an additional column you can use data validation to limit the input, or conditional formatting to change the colour of the background or you can use both.
Select "Data>Validation..." to limit the input to whole number between 1000 & 9999
Select "Format>Conditional Formatting..." to change the format of the cell. You can set 2 conditional formats to pick up the errors, 1 for cell values less than 1000 and 1 when greater than 9999.
If you need more help in using these options post back.

20040503, 14:27 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Validation formula or function? (Excel2000)
I would tend to combine them all into 1 formula, if you want the same formatting for each "failure".
You only are allowed 4 different formats (3 conditions) so you might not want to waste them.
Steve

20040503, 14:32 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
Re: Validation formula or function? (Excel2000)
Steve,
nice formula but what about an entry of 25.1?
zeddy

20040503, 14:34 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Validation formula or function? (Excel2000)
It is <1000, but how about:
=and(isnumber(a1), a1>=1000, a1<=9999,len(a1)=4)
Steve

20040503, 16:58 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
Re: Validation formula or function? (Excel2000)
Steve,
The formula does the job.
But would I be better off assigning this to a custom function?
zeddy

20040503, 16:59 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Validation formula or function? (Excel2000)
No. As I stated earlier, formulas are much faster than UDFs
Steve

20040503, 17:10 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
Re: Validation formula or function? (Excel2000)
Steve,
OK, formulas would be faster.
Would I gain any reduction in file size?
I'm trying to work out what the tradeoff would be.
Smaller files load faster from a network.
The time saved (in loading) even allowing for a slower calc may still be an advantage for me overall.
zeddy

20040503, 17:28 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Validation formula or function? (Excel2000)
Try it and see and let us know what you discover. You will still have to have all the formulas so I can't imagine the file size will be that much smaller.
If you are always looking to calculate the same "relative cell" from where the formula is, a "little used" that can decrease the number of formulas is to use a "named formula"
If you always want to look at the cell to the immediate left and your first number is in cell A1 (so you want the formula in B1)
Select B1
Insert  name  define
Name: IsValidExt
Refers to:
<pre>=and(isnumber(a1), a1>=1000, a1<=9999,len(a1)=4)</pre>
<ok>
Now enter into B1 the formula:
<pre>=isvalidext</pre>
and copy it down the rows, each one will look at the cell to the left and report the results
Again, I am not sure how much memory this will save or how much calc time it will save. I suggest experimenting.
Steve

20040503, 19:15 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,048
 Thanks
 145
 Thanked 543 Times in 518 Posts
Re: Validation formula or function? (Excel2000)
Many thanks Steve
..the formulas will be a lot shorter (particularly with some complex ones).
I'd forgotten about 'named formulas'
Brilliant!
Now I will test all three options and report back.
Thanks again. Very helpful!
zeddy