Results 1 to 8 of 8

Data Validation issue (Decimal Places)

This is a discussion on Data Validation issue (Decimal Places) within the Excel Questions forums, part of the Question Forums category; Aloha Excel Guru’s. I am hoping someone can provide a way to provide better validation than Excel’s default decimal validation ...

  1. #1
    New Member
    Join Date
    Jan 2007
    Location
    Honolulu, HI
    Posts
    23

    Default Data Validation issue (Decimal Places)

    Aloha Excel Guru’s.

    I am hoping someone can provide a way to provide better validation than Excel’s default decimal validation (Excel 2007). I have cells configured to only accept decimal values between 0 and 100. I need to further restrict the input values to one decimal place.

    Desired Result: Accept “70.1” as valid data but not “70.11”


    Thanks in advance.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Data Validation issue (Decimal Places)

    Quote Originally Posted by chadmiller View Post
    Aloha Excel Guru’s.

    I am hoping someone can provide a way to provide better validation than Excel’s default decimal validation (Excel 2007). I have cells configured to only accept decimal values between 0 and 100. I need to further restrict the input values to one decimal place.

    Desired Result: Accept “70.1” as valid data but not “70.11”


    Thanks in advance.
    Hi Chad Miller:

    How about trying the following DataValidation formula for cell A1:

    =LEN(MID(A1,FIND(".",A1)+1,255))=1

    I hope this helps.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    LxQ
    LxQ is offline
    Board Regular LxQ's Avatar
    Join Date
    Feb 2006
    Location
    CA
    Posts
    579

    Default Re: Data Validation issue (Decimal Places)

    if you want to enter this in G29.. like I just happened to be in that cell.. then use this formula:

    =IF(G29=INT(G29*10)/10,IF(G29>=0, IF(G29<=100,TRUE,FALSE),FALSE),FALSE)

    First, this takes the integer of g29*10, so it actually moves the decimal point, then divides by 10,.. and if that's equal to what was entered, then there were no more than 1 decimal points entered...

    then I had to add two more IF statements just to limit it between 0 and 100

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,882

    Default Re: Data Validation issue (Decimal Places)

    Perhaps:

    =AND(TRUNC(G29,1)=G29,G29>=0,G29<=100)
    Office 2007/2010

  5. #5
    New Member
    Join Date
    Jan 2007
    Location
    Honolulu, HI
    Posts
    23

    Default Re: Data Validation issue (Decimal Places)

    Thank you, I seem to get a few of your solutions to work in a different cell but can I put a formula in the actual data validation seciton?

    Hopefully this is not a silly questions cuz I can't find an option that will accept the formula.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Data Validation issue (Decimal Places)

    Quote Originally Posted by chadmiller View Post
    Thank you, I seem to get a few of your solutions to work in a different cell but can I put a formula in the actual data validation seciton?

    Hopefully this is not a silly questions cuz I can't find an option that will accept the formula.
    Hi Chad:

    To apply DataValidation to cells B1:B10 ...

    1) select cells B1:B10
    2) make cell B1 as the ActiveCell
    3) Invoke DataValidation ... settings, allow|Custom, use the following formula
    4) =LEN(MID(B1,FIND(".",B1)+1,255))=1

    that should do it.

    I hope this helps.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,882

    Default Re: Data Validation issue (Decimal Places)

    Yogi,

    That doesn't limit the range to 0-100.

    Although the op may not want whole numbers, your formula doesn't allow them.

    Perhaps if a forced decimal place is required:

    =AND(LEN(MID(B1,FIND(".",B1)+1,255))=1,B1>=0,B1<=100)
    Last edited by Scott Huish; Apr 17th, 2008 at 08:43 PM.
    Office 2007/2010

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Data Validation issue (Decimal Places)

    Quote Originally Posted by HOTPEPPER View Post
    Yogi,

    That doesn't limit the range to 0-100.

    Although the op may not want whole numbers, your formula doesn't allow them.

    Perhaps if a forced decimal place is required:

    =AND(LEN(MID(B1,FIND(".",B1)+1,255))=1,B1>=0,B1<=100)
    Hi HOTPEPPER:

    Thanks for pointing out about the 0 to 100 range. I was focussing on 1 significant digit after the decimal point and overlooked the 0 to 100 range issue. That can be added as AND condition to my formula.

    My proposed solution is one way and not necessarily the best, the most efficient, or the shortest.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com