Using quartiles in Numbers

Question

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

Reply

Answer 1

Oct 6, 2016 1:23 PM in response to Community User

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.

Reply

Answer 2

Oct 6, 2016 1:34 PM in response to Wayne Contello

here is a way I would approach this...

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)

Reply

Answer 3

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.

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

32 %

45 %

91 %

45 %

27 %

45 %

55 %

27 %

82 %

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 %

30 %

59 %

45 %

59 %

45 %

64 %

50 %

36 %

32 %

73 %

68 %

45 %

41 %

39 %

55 %

50 %

45 %

41 %

Reply

Answer 4

Oct 6, 2016 1:58 PM in response to Community User

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.

Reply

Answer 5

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..!

Reply

Answer 6

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%..

Reply

Answer 7

Barry

Level 9

60,023 points

Oct 6, 2016 5:00 PM in response to Community User

"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.

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

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

Regards,

Barry

Reply

Answer 8

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.

Reply

Answer 9

SGIII

Level 8

37,550 points

Oct 7, 2016 9:41 AM in response to Community User

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

Reply

Answer 10

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?

Reply