Ellen Katrine

Q: Using quartiles in Numbers

I'm trying to figure out the first and third quartile from a long list of different values, by using Numbers. I've already done it once in Excel, but for some reason I can't get the same result in Numbers. In Excel my first quartile is 40%, but when I do it with the same exact values in Numbers, I get 41%. When I try to find the third quartile the result is 57% in Excel, and 55% in Numbers. I've copied the list of values from Excel, so I'm sure there's no mistake with any numbers missing or anything like that. The list has 80 values in it, though that really shouldn't matter. Would be great if anyone knew what is going on with this!

MacBook Pro (Retina, 13-inch, Mid 2014), OS X El Capitan (10.11.6)

Posted on Oct 6, 2016 12:32 PM

Close

Q: Using quartiles in Numbers

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Oct 6, 2016 1:23 PM in response to Ellen Katrine
    Level 6 (19,357 points)
    iWork
    Oct 6, 2016 1:23 PM in response to Ellen Katrine

    Maybe something is not right?

     

    Maybe something is different?

     

    OK.  All kidding aside.  We cannot help if you do not provide the details (like the data).  Paste a copy of the data (select the data, copy, click in this forum, paste) and screenshots of the results in excel.  Post the formulas you are using in excel and what you are using in Numbers.

  • by Wayne Contello,

    Wayne Contello Wayne Contello Oct 6, 2016 1:34 PM in response to Wayne Contello
    Level 6 (19,357 points)
    iWork
    Oct 6, 2016 1:34 PM in response to Wayne Contello

    here is a way I would approach this...

    Screen Shot 2016-10-06 at 3.27.38 PM.png

     

    data is in column A

    column B contains a number indicating the quartile the value is in

     

    B1=FLOOR((A1−MIN(A))÷((MAX(A)−MIN(A))÷4), 1)+1

     

    this is shorthand for... select cell B1, then type (or copy and paste from here) the formula:

    =FLOOR((A1−MIN(A))÷((MAX(A)−MIN(A))÷4), 1)+1

     

    select cell B1, copy

    select cells B1 thru the end of column B, paste

     

    now count each occurrence as follows:

    D1=COUNTIF(B, 1)

    D2=COUNTIF(B, 2)

    D3=COUNTIF(B, 3)

    D4=COUNTIF(B, 4)

  • by Ellen Katrine,

    Ellen Katrine Ellen Katrine Oct 6, 2016 1:47 PM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Oct 6, 2016 1:47 PM in response to Wayne Contello

    Thanks for the quick reply!

    The data is at the bottom of this comment. Here's the screenshot, where you can also see what formula I used in excel.
    ovrenedrekvartil.png

     

    And here's what I write in Numbers (sorry this is in norwegian, but "kvartil" translates to "quartile" and "første" means "first"):

    numbers.png

    32 %

    45 %

    91 %

    45 %

    27 %

    45 %

    55 %

    27 %

    82 %

    50 %

    50 %

    64 %

    50 %

    45 %

    82 %

    55 %

    50 %

    41 %

    59 %

    45 %

    50 %

    55 %

    50 %

    32 %

    27 %

    41 %

    32 %

    14 %

    64 %

    41 %

    55 %

    32 %

    27 %

    45 %

    50 %

    45 %

    64 %

    45 %

    36 %

    59 %

    73 %

    82 %

    55 %

    36 %

    55 %

    64 %

    27 %

    32 %

    55 %

    45 %

    41 %

    27 %

    59 %

    50 %

    59 %

    64 %

    50 %

    59 %

    55 %

    36 %

    36 %

    30 %

    59 %

    45 %

    59 %

    45 %

    64 %

    50 %

    36 %

    32 %

    73 %

    68 %

    45 %

    41 %

    41 %

    39 %

    55 %

    50 %

    50 %

    45 %

    41 %

  • by Wayne Contello,

    Wayne Contello Wayne Contello Oct 6, 2016 1:58 PM in response to Ellen Katrine
    Level 6 (19,357 points)
    iWork
    Oct 6, 2016 1:58 PM in response to Ellen Katrine

    I suggest removing the space and the percent sign " %" by using search and replace.

     

     

    Ignore my previous post as it was not addressing your question

     

     

    I get the same result that you report for Numbers.  I would expect Numbers and Excel to agree.  I suggest doing this by hand and getting the "REAL" answer to see which application is correct.

  • by Ellen Katrine,

    Ellen Katrine Ellen Katrine Oct 6, 2016 2:20 PM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Oct 6, 2016 2:20 PM in response to Wayne Contello

    Unfortunately I can't paste the formula, probably because it's different in norwegian. I tried to just translate it, but keep the parentheses the same, -that doesn't work either. I'm not sure, I might have misunderstood something about this method, but wouldn't this take quite a long time to do for that much data..?

    You don't happen to know or have any idea of why I almost got the right answer, only 1-2% off, using the quartile-formula? Kind of annoying when I'm so close, but yet so far..!

  • by Ellen Katrine,

    Ellen Katrine Ellen Katrine Oct 6, 2016 2:49 PM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Oct 6, 2016 2:49 PM in response to Wayne Contello

    Well it's good to hear that it's not just me, thought I was going crazy for a minute here. By hand, the median is 41. Then counting 20 rows from 41 each way, in order to make all of the quartiles equal, I land in between 39% and 41%. For the third quartile, I land between 55% and 59%. So I see how Excel finds that the quartiles are 40% and 57%. I'm just not sure I've done this the right way, and I still wonder why Numbers is choosing to give me 41% and 55%..

  • by Barry,

    Barry Barry Oct 6, 2016 5:00 PM in response to Ellen Katrine
    Level 7 (32,582 points)
    iWork
    Oct 6, 2016 5:00 PM in response to Ellen Katrine

    "The list has 80 values in it, though that really shouldn't matter."

     

    Actually, the list you provided has 81 values in it, and that is probably the source of the difference in interpretation.

     

    Below is the list (column A), sorted in ascending order.

    In column B, B2 contains the count of the values in the list.

    The rest of the cells show the position of each score in the sorted list, lowest in position 1, highest in position 81.

    Q1, Q2, Q3 (and Q4) are displayed in column C.

     

    The quarter marks of the count are 20.25, 40.5 and 60.75.

     

    Numbers appears to picking the score that appears on the count one above the integer value of a mark having a fractional part. Excel appears to be interpolating between the scores immediately above and below the mark value.

     

    I've hidden several rows to make the top and bottom (and quarter marks fit into a screen shot.

     

    Screen Shot 2016-10-06 at 4.57.15 PM.png

    For what it's worth, LibreOffice returned 41, 50 and 55

     

    Was the Excel result from Excel for Windows or for Mac?

     

    Regards,

    Barry

  • by Ellen Katrine,

    Ellen Katrine Ellen Katrine Oct 7, 2016 12:32 AM in response to Barry
    Level 1 (4 points)
    iWork
    Oct 7, 2016 12:32 AM in response to Barry

    I noticed it has 81 values in it, my mistake when writing that comment! I would still expect the different programs to have the same result, though. As you can see, I did the same thing as you and found how Excel is interpolating, but which answer is more correct? (If there even is one)

    I've tried Excel for both Windows and Mac, still the same answer.

  • by SGIII,Solvedanswer

    SGIII SGIII Oct 7, 2016 9:41 AM in response to Ellen Katrine
    Level 6 (10,782 points)
    Mac OS X
    Oct 7, 2016 9:41 AM in response to Ellen Katrine

    Excel has multiple quartile functions, as described here.

     

    QUARTILE.ESC, QUARTILE.INC and QUARTILE have subtle differences.

     

    QUARTILE.ESC excludes the median when determining interpolation. QUARTILE.INC and QUARTILE include the median.

     

    The QUARTILE function in Excel and in Numbers give the same results on my machine.

     

    SG

  • by Ellen Katrine,

    Ellen Katrine Ellen Katrine Oct 7, 2016 9:48 AM in response to SGIII
    Level 1 (4 points)
    iWork
    Oct 7, 2016 9:48 AM in response to SGIII

    That must be why! But Numbers doesn't have any other quartile functions except "quartile", right? If I wanted to use something similar to quartile.esc with numbers, do you know how I would do that?