Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

List Only Whole Numbers From Decimal Numbers

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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, 04:17.
    AAE
    ----------------------------------------------------

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

    Comment


    • #3
      Re: Delete Numbers That Display Cents

      I use something like:

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

      Comment


      • #4
        Re: Delete Numbers That Display Cents

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

        Comment


        • #5
          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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X