Results 1 to 12 of 12
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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 four-digit 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

  2. #2
    2 Star Lounger
    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 built-in formulas tend to be faster, so might consider that.

  3. #3
    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: 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

  4. #4
    Platinum Lounger
    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.

  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: 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

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Validation formula or function? (Excel2000)

    Steve,

    nice formula but what about an entry of 25.1?

    zeddy

  7. #7
    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: Validation formula or function? (Excel2000)

    It is <1000, but how about:

    =and(isnumber(a1), a1>=1000, a1<=9999,len(a1)=4)

    Steve

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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

  9. #9
    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: Validation formula or function? (Excel2000)

    No. As I stated earlier, formulas are much faster than UDFs

    Steve

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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

  11. #11
    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: 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

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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

Posting Permissions

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