Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enforce 4 decimal place numeric input (Excel 2003, SP2)

    I have a user who has a large, 24k rows by 58 columns, spreadsheet in which they record percentages, potentially against each row/column intersection.
    How can I enforce the numeric data entry to only allow four decimal places?
    I use Access to validate their data entry before exporting to a csv file for subsequent use by another process. Yes, it's convoluted.
    From Access I can identify those entries that have more than four decimal places, but I would like to enforce that at data entry level within Excel, if possible.

    Thanks in advance
    Zinger

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enforce 4 decimal place numeric input (Excel 2003, SP2)

    Zinger,

    Use the VALUE and FIXED functions like this =VALUE(FIXED(A1,4)).

    if you do not mind the number being displayed as text, just =FIXED(B2,4) would do the trick.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Enforce 4 decimal place numeric input (Excel 2003, SP2)

    You could set the number format of the cells to display 4 decimal places (in the Number tab of Format | Cells...)
    And in the Calculate tab of Tools | Options..., tick the 'Precision as displayed' check box. This will cause all numbers to be stored in the workbook exaxtly as they are displayed on screen. This is a per workbook setting, it will affect all sheets in that workbook, but not other workbooks.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enforce 4 decimal place numeric input (Excel 2003, SP2)

    Sorry for the delay in responding, but I have been flat out with UAT.
    Thank you for your responses.
    The user has decided to take responsibility for only entering four decimal places and I have passed on both answers to them.
    Thanks Zinger

Posting Permissions

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