+ Reply to Thread
Results 1 to 6 of 6

Getting wrong results for frequency function and countif functions

  1. #1
    Registered User
    Join Date
    08-13-2015
    Location
    US
    MS-Off Ver
    2016
    Posts
    2

    Getting wrong results for frequency function and countif functions

    Well, I'm wanting to get the Frequencies in the G column the same as what I get from the I column by using the frequencies function or any other function to make it the easiest.

    Also secondary issues, I'm confessed with why I'm getting different results in two countif functions, and why sometimes adding .5 happens and doesn't.. There is something causing this in excel with how I have it set up. I have them highlighted in yellow for those in my excel sheet download.

    Here's the download to my excel document:
    https://drive.google.com/file/d/0Bxm...ew?usp=sharing

    Thanks for the help!
    Attached Files Attached Files
    Last edited by KurtBliss; 08-13-2015 at 05:41 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Getting wrong results for frequency function and countif functions

    Hi, and welcome to the forum.

    Please upload the file to this forum. Many of us are reluctant to visit sites of unknown provenance.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Getting wrong results for frequency function and countif functions

    As one who went ahead and downloaded it anyway, it looks to me like it is a combination of failure to understand the FREQUENCY() function and rounding error.

    The FREQUENCY function counts bins as Previousbin<x<=thisbin. This means you should be using the UPPER column rather than the LOWER column as your bins range.
    The division in C6 returns a decimal value, even though you display to 0 decimal places. So your bins end up being 54,59.5,etc. Your "book" results suggest that bins should be 59,65,etc. I put a ROUND(currentC6,0) formula into C6.

    With those two changes, I get the book results. It is up to you to decide if the book results are more correct than the results you have.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Getting wrong results for frequency function and countif functions

    Because some of you bins are decimals. Perhaps, that's why?

  5. #5
    Registered User
    Join Date
    08-13-2015
    Location
    US
    MS-Off Ver
    2016
    Posts
    2

    Re: Getting wrong results for frequency function and countif functions

    Thank you rounding and switching to upper bounds worked.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Getting wrong results for frequency function and countif functions

    Maybe this will help in understanding how the FREQUENCY function works...

    Data Range
    A
    B
    C
    1
    Data
    Bins
    Frequency
    2
    0
    0
    2
    3
    0
    25
    3
    4
    2
    50
    2
    5
    13
    75
    1
    6
    16
    100
    2
    7
    31
    0
    8
    48
    9
    69
    10
    90
    11
    91
    12
    ------
    ------
    ------


    Select the range C2:C7 then array** enter this formula:

    =FREQUENCY(A2:A11,B2:B6)

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    FREQUENCY returns an array of "count ifs".

    Based on the posted sample those count ifs are as follows:

    Count if A2:A10 is less than or equal to 0 = 2
    Count if A2:A10 is greater than 0 and less than or equal to 25 = 3
    Count if A2:A10 is greater than 25 and less than or equal to 50 = 2
    Count if A2:A10 is greater than 50 and less than or equal to 75 = 1
    Count if A2:A10 is greater than 75 and less than or equal to 100 = 2
    Count if A2:A10 is greater than 100 = 0
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Countif resulting in wrong results
    By Krameht in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2015, 03:51 PM
  2. Replies: 6
    Last Post: 02-12-2015, 04:08 PM
  3. Retrieving results and FORECAST function - the results are wrong!
    By Lucky_G in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-03-2014, 10:57 AM
  4. Frequency function to correct ranking instead of countif
    By Scottlarock in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-03-2013, 07:40 PM
  5. COUNTIF giving wrong results
    By Excel_vba in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2013, 04:17 PM
  6. Countif results wrong
    By Markshr in forum Excel General
    Replies: 5
    Last Post: 02-08-2012, 02:56 AM
  7. Combining Functions Countif & Frequency
    By vagabond in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-10-2010, 08:04 AM

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