0

Suppose I have the following grouped data for mass of dogs:

Mass         Frequency

0 to 5          13

6 to 10         28

11 to 15        47

16 to 20        21

21 to 25        11

25 to 30         6

How can I use Excel to estimate the first quartile?

I know how to make an ogive (using a scatter diagram with points connected by a smooth curve), and I can use the ogive and my eyes (and maybe a ruler placed on the screen) to roughly find the first quartile. But can Excel give me a more rigorous answer?

I don't want to use the "Add trendline" function, because a trendline is not really an ogive (a trendline does not go through all the points).

0

This is grouped data so unless you have the raw data, you're going to have to do something to recreate variation in your data. For simplicity we can assume a flat distribution - dogs are as equally likely to be 0 as 5.

Use the REPT() function to output a comma-separated list of numbers representing each group.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

Use REPT

At the bottom, concatenate each of those strings into one large string:

Long string

Copy this string and Paste Special as Values in a new worksheet. Use the Text to Columns tool on the Data ribbon tab to split the data into one column per value. Copy this entire range, then Paste Special and Transpose to flip this into a vertical list of values. Your data should look something like this:

transposed data

Feel free to discard the horizontal row - we don't need it. Now we want to interpolate some values in column B, using a formula something like:

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

Breaking this down, we have:

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

Now, you can use the QUARTILE() function on this list of estimated values to approximate your quartiles:

=QUARTILE($B$5:$B$130,1)

enter image description here

| improve this answer | |
0

Graphically, you want to make a histogram with the "bins" set to quartiles. You can look up how to do that.

Alternatively, you can use a formula. I'll describe this in words, and you can figure out the math.

There are 126 dogs. 25% of that is 31.5. You want to find the weight that the 31.5 lightest dogs would weigh less than.

There are 13 dogs in your lightest bin, so they get counted. That leaves 18.5 dogs, but the next bin has 28 dogs. So the question is: what's the weight of the 18.5th lightest dog in the second bin?

Using what's called linear interpolation, you can estimate that the unknown weight is 18.5/28 of the way between 6 and 10 pounds. That's 6+4*(18.5/28).

You can use the same approach to find the other quartiles, if you need to.

The language here is a bit awkward, but you get the idea. I hope this helps.

EDIT: If you know the weights of all the individual dogs, just rank them in order and calculate the average weight of the 31st and 32nd dogs.

| improve this answer | |
  • PS. I get 8.64 lbs using this approach. – Bandersnatch Aug 11 '17 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.