+ Reply to Thread
Results 1 to 2 of 2

How? Count only numbers with 3 or more decimal places.

Hybrid View

  1. #1
    Registered User
    Join Date
    03-19-2008
    Posts
    28

    Question How? Count only numbers with 3 or more decimal places.

    (1) I need a formula in a cell that monitors my Column (H14:H5000).
    The data is inserted into this column by a macro from an entry screen.
    I need to be alerted by a number count or statement that I have entries exceeding 2 decimal places in this column. Entries in this column are supposed to have only 2 decimal places: 25.65, but sometimes a 3rd digit: 25.655 (not visible) is hit in error.

    (In the future I would like to be alerted at the point of first entry before the macro is run, but for now I have this whole column to check)

    (2) Next I need a method other than manually scaning for these errant entries. I need to identify the location(s) for correction.

    Thanks

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    You could use conditional formatting with the formula =ROUND(A1,2) <> A1 to highlight them, or this array formula to locate the first errant value:

    =MATCH(TRUE, ROUND(H14:H5000, 2) <> H14:H5000, 0)
    Last edited by shg; 03-21-2008 at 11:45 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1