+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 FIND & Select tool cannot find data that exist in workbook

  1. #1
    Registered User
    Join Date
    03-02-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    7

    Excel 2007 FIND & Select tool cannot find data that exist in workbook

    I am running MS Excel 2007. One of the workbooks is my Daily journal with about 20000 rows. Column A is formatted for date. Column B is for description of daily activity and column C is formatted for currency. Often I use the FIND & Select tool to find data related to past activities. Let's say I am searching for a currency amount of 42,500.55 in column C. I enter 42,500.55 or 42500.55 in the FIND tool and neither returns a result. But if I enter 42500 then it finds the data. I like to know why Excel cannot find the result by the actual recorded number in the workbook.
    I appreciate your help.

    Shardon

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,087

    Re: Excel 2007 FIND & Select tool cannot find data that exist in workbook

    I like to know why Excel cannot find the result by the actual recorded number in the workbook.
    My guess is that the exact data 42500.55 is not present in the actual number. You don't say where the example value 42500.55 comes from, but I could easily see a computation scenario where floating point error causes that result to actually be 42500.54999999999. This will display as 42500.55, but will not be found when searching for 42500.55. Floating point error is not the only possible scenario where a value displayed as 42500.55 is not exactly 42500.55. One way or another, my guess is that, what you think is 42500.55 is not exactly 42500.55 so that Find cannot find it that way.

    Assuming this explains why Find cannot find these values, fixing it will depend on exactly how these values are entered or calculated, whether the problem is floating point error or some other rounding error, and exactly what the value in those cells should be.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    03-02-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Excel 2007 FIND & Select tool cannot find data that exist in workbook

    MrShorty
    Thanks for replying to my question. You are right. First let me make a correction. The actual amount in column C is 42500.50 not 42500.55 and all the amount in column C is currency and there is no formula used with any number. So I clicked on the value and the formula bar showed 42500.5 instead of 42500.50. I formatted the entire column under currency with 2 decimal places and the formula bar still shows the value as 42500.5 not 42500.50
    How should I format the column so the actual number in the formula bar be represented as 42500.50
    Thanks in advance

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,832

    Re: Excel 2007 FIND & Select tool cannot find data that exist in workbook

    1. if you have a value of 42500.5, you dont need to search for 42500.50, excel will drop that last 0 anyway
    2. formatting only changes the visual display of how a cell contents is shown - it does not change the actual cell contents.
    3. Are you sure your numbers really ARE numbers, and not text looking like a number? Test with =ISNUMBER(cell-ref) FALSE indicates text and we will need to convert it
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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