+ Reply to Thread
Results 1 to 17 of 17

Countif Decimal is within a range

  1. #1
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Question Countif Decimal is within a range

    Hello,

    I have a list of almost 200 numbers, all of them to the nearest hundredth point.
    What I am trying to accomplish is to do a CountIf(Decimal points is between .98 and .02) regardless of what the whole number is.
    I keep getting and answer of 0
    Please Login or Register  to view this content.
    Example
    5.00
    6.45
    7.86
    3.98
    2.99
    4.01
    12.02
    8.24

    Should = 5

    Any Assistance would be appreciated!
    Attached Files Attached Files
    Last edited by Bryan.Pagenkopf; 10-24-2018 at 01:18 PM. Reason: New Example File

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,581

    Re: Countif Decimal is within a range

    first stab which isnt elegant
    =SUMPRODUCT((MOD(A1:A8*100,100)<=2)*(MOD(A1:A8*100,100)>=0))+SUMPRODUCT((MOD(A1:A8*100,100)>=98)*1)

    or perhaps

    =SUMPRODUCT((MOD(A1:A8*100-98,100)<=4)*1)
    Last edited by davsth; 10-24-2018 at 11:27 AM.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,776

    Re: Countif Decimal is within a range

    Please try
    =SUMPRODUCT((MOD(E18:E200-0.02,1)<=0.96)*(E18:E200<>""))

  4. #4
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Re: Countif Decimal is within a range

    Davsth and Bo_Ry,

    Thanks, those were close but it's still not hitting the proper end value. I attached an Example File for reference.
    Thank you for the help!

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Countif Decimal is within a range

    You've got rounding error (i.e. displayed number isn't what's stored, should be rounded to nearest 2nd decimal). Something like...
    =SUMPRODUCT((ROUND(B3:B367,2)<=0.02)+(ROUND(B3:B367,2)>=0.98))

    Or change B column formula to...
    =Round(A3/$A$2,2)

    And use simple COUNTIF with addition.
    COUNTIF(B3:B367,"<=0.02")+COUNTIF(B3:B367,">=0.98")
    Last edited by CK76; 10-24-2018 at 12:06 PM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Countif Decimal is within a range

    =sumproduct(--(abs(round(b3:b367, 2) - round(b3:b367, 0)) <= 0.02))
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Re: Countif Decimal is within a range

    shg,

    That worked on my example file without issue! When I added it into my actual document I am getting a "#value" error.

    =SUMPRODUCT(--(ABS(ROUND(E18:E200, 2) - ROUND(E18:E200, 0)) <= 0.02))

    Any Ideas Why?

    Thank you again!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Countif Decimal is within a range

    Maybe your numbers are text masquerading as numbers?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Countif Decimal is within a range

    No idea why.

  10. #10
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Re: Countif Decimal is within a range

    They are set as Numbers in the Cells. I do have them in the Cell via a VLOOKUP function but even the source Cells are set as numbers.

  11. #11
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Re: Countif Decimal is within a range

    I've Uploaded a new Example File

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,776

    Re: Countif Decimal is within a range

    Please try
    =SUMPRODUCT(--(MOD(ROUND(B3:B367+0.02,2),1)<=0.04))

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Countif Decimal is within a range

    It's floating point issue.

    Though @Bo_Ry's formula is more efficient, below would work as well.
    =SUMPRODUCT((ROUND(E3:E367-TRUNC(E3:E367),2)<=0.02)+(ROUND(E3:E367-TRUNC(E3:E367),2)>=0.98))

  14. #14
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Re: Countif Decimal is within a range

    I keep getting the #VALUE

    Capture3.PNG

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,581

    Re: Countif Decimal is within a range

    Bryan

    Can you upload a sample file with the formulas that are giving you the error?
    If posting code please use code tags, see here.

  16. #16
    Registered User
    Join Date
    09-08-2018
    Location
    Prescott, WI
    MS-Off Ver
    365
    Posts
    36

    Re: Countif Decimal is within a range

    Norie, I can't attach my file but I did display the formulas used just above each #value error result in the picture above.
    Last edited by AliGW; 10-25-2018 at 12:34 PM. Reason: Unnecessary quotation removed.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Countif Decimal is within a range

    Yes, you can attach your file.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

+ 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. Replies: 4
    Last Post: 02-24-2017, 02:06 AM
  2. [SOLVED] Sum of range picking up ^-10 decimal place. Is there a way to sum 2 decimal place only?
    By graym463 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2015, 08:48 AM
  3. SOLVED Countif formula with 4 decimal points
    By toclare84 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-24-2015, 11:57 AM
  4. Replies: 2
    Last Post: 10-03-2012, 03:38 PM
  5. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM
  6. count decimal nos in a range
    By sheryar in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-12-2009, 05:39 AM
  7. [SOLVED] COUNTIF or not to COUNTIF on a range in another sheet
    By Ellie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2005, 05:06 PM

Tags for this Thread

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