Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: List Only Whole Numbers From Decimal Numbers

  1. #1
    Join Date
    30th March 2003
    Location
    USA
    Posts
    126

    List Only Whole Numbers From Decimal Numbers

    From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed


    For example

    34.95
    21.88
    21.00
    56.00
    45.77

    Only those valaues ending in .00 will list

    I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents

    Many thanks
    Leslie Yambor
    Anne Arundel County Public Schools

  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Delete Numbers That Display Cents

    Perhaps you could modify this formula to suit. It assumes two decimal places, however, as far as Excel is concerned if the number in your cell is "25" (whole number), but is formatted to show "25.00" (a decimal), Excel does not recognize the decimal or the "zero" characters after it.

    The above explanation is so that you understand that an error value will occur with the formula in the first "IF" function and the assumption is that the value in the cell, for which the error is returned, is a whole number in reality.

    =IF(ISERR(TEXT(MID(A4,FIND(".",A4)+1,2),"#")*1),TRUE,IF((TEXT(MID(A4,FIND(".",A4)+1,2),"#")*1)>0,FALSE,A4))
    Use this formula in a helper column, then filter on TRUE values to add or FALSE values to delete rows.

    Edit: If you use the DSUM function in conjuction with the helper column, you don't need to delete any cells. Give the helper column a header, then use a criteria cell in a top row above the data and reference the crtieria cells when using DSUM.
    Last edited by AAE; May 23rd, 2009 at 04:17.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    18th January 2009
    Posts
    63

    Re: Delete Numbers That Display Cents

    I use something like:

    =IF(MOD(A1,1)<>0,0,A1)
    or
    =IF(INT(A1)<>A1,0,A1)

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th March 2003
    Location
    USA
    Posts
    126

    Re: Delete Numbers That Display Cents

    Many thanks - this worked perfectly
    HAve a wonderful weekend
    Leslie Yambor
    Anne Arundel County Public Schools

  5. #5
    Join Date
    30th March 2003
    Location
    USA
    Posts
    126

    Re: Delete Numbers That Display Cents

    Thank you - I would never have worked this equation up
    it is great and I can use it for many different things
    appreciate your time


    HAve a wonderful weekend
    Leslie Yambor
    Anne Arundel County Public Schools

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Numbers To Contain Decimal
    By cmarsellus in forum EXCEL HELP
    Replies: 1
    Last Post: May 24th, 2007, 23:53
  2. Whole Numbers Being Changed To Decimal
    By musicman99 in forum EXCEL HELP
    Replies: 2
    Last Post: April 29th, 2007, 02:53
  3. Convertering All Numbers To Decimal
    By hooksetter in forum EXCEL HELP
    Replies: 3
    Last Post: February 16th, 2007, 21:39
  4. Replies: 3
    Last Post: May 27th, 2006, 00:33
  5. Dealing with decimal numbers
    By rbjexcpa in forum EXCEL HELP
    Replies: 4
    Last Post: September 23rd, 2004, 12:50

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
  •  
porno