+ 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
    11

    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
    12,292

    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
    11

    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
    49,593

    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)

Similar Threads

  1. [SOLVED] Find the data from another sheet, if the input value does not exist leave blank
    By oozden in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2014, 02:32 AM
  2. How to find if sheet exist in workbook
    By Harrij in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2013, 07:19 AM
  3. Select Time in a cell then find it in another workbook.
    By Abbey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2012, 09:32 AM
  4. Replies: 0
    Last Post: 06-15-2012, 09:50 AM
  5. Display data does not exist when using .FIND
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2010, 04:33 PM
  6. cant find tool "Data analysis" in excel 2007
    By Anzar in forum Excel General
    Replies: 2
    Last Post: 06-14-2009, 11:14 AM
  7. Using the Find tool in EXCEL
    By TK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2005, 04:06 PM

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