Excel Box and Whiskers

[ErwinVanLoo]

I made a graph "Bow and Whiskers" with 5 values: (see bitmap at the end of this message)

Min = 32,5

Q1 = 37

Q2 = 39

Q3 = 40

max = 43

(and choose for the option inclusive median)

A nice box with 2 wiskers appears in a diagram where the vertikal axis give me values from 30 to 44

When I dminish te minimum value lower than 32,5 !! then the the whisker under the box disappear and a point appears instead ...

Why is the whisker disappearing?

How can I get with the minimum value equal for example to 32 the missing whisker (without changing the option inclusive median)?

I want to get the next result:

- A graphic box and TWO wiskers whith the values 

Min = 32

Q1 = 37

Q2 = 39

Q3 = 40

max = 43

Why is the second whisker dissapearing?

Is there a word around or update for this problem (without changing the values for Q1, Q2, Q3 and max?

Thanks

Erwin

[#2]

Hello @ErwinVanLoo,

Box and whisker plots are designed such that data points that are outliers are plotted outside of the "whisker". An outlier is considered to be a data point that is 1.5 times the interquartile above the third quartile or below the first quartile. The interquartile range for this data set is Q3-Q1 or 40-37=3. To determine the range for outliers, that would be:

• Q1-(1.5*3)=37-4.5=32.5
• Q3+(1.5*3)=40+4.5=44.5

So, values below 32.5 and above 44.5 will be plotted outside of the "whiskers". In your data set, since 37 is the lowest data point (and not an outlier), it become the lower "whisker".

I hope this makes sense!

[#3]

@PReagan

Hello,

Thanks for your reaction, now I understand the reason why Whiskers suddenly appears / disappears.

It's a pitty that the behaviour of the Whiskers is not (well) documented.

Above all it is not the normal behaviour that can be expected from a specific whisker nor from the box and whiskers.

The given reason is not the way we expect that whiskers behave or should behave.

Is it possible to show always the wiskers and not to replace a whisker by a point if the distance is big?

We always need to draw the whiskers in statistical problems, that's why I made one document to have the different graphs (bar graphs, histograms, boxplot...) and in each boxplot I need one box and 2 whiskers.

Is it possible to make changes to the software so that the box and 2 whiskers will always be fully displayed?

Greetings

Erwin  

[#4]

Hello @ErwinVanLoo,

If Q1 is equal to the lowest (non-outlier) value of a data set, this will become the lower whisker. Similarly, if Q3 is equal to the highest (non-outlier) value of a data set, this will become the upper whisker. There is no way to change this in excel.

I hope this helps!

[#5]

Hello @PReagan,

Sorry I don't understand your explenation. I took your explanation and have put my values of my first post beside.

I read then:

If Q1 (in my example in the first post is 37) is equal to the lowest (non-outlier) value of a data set (in my example in the firs post = min 32,4999 ??), this will become the lower whisker (No I don't see a Whisker).

This makes no sense. Where didn't I understand your explanation?

Please help me with the values

min = 32

Q1 = 37

Q2 = 39

Q3 = 40

max = 43

to have a graphic with 2 whiskers.

Greetings

Erwin

[#6]

@ErwinVanLoo,

In this example, 32 is an outlier because it is less than 1.5 times the interquartile range below the lower quartile:

= Q1 - 1.5 * (Q3 - Q1)
= 37 - 1.5 * (40 - 37)
= 37 - 1.5 * 3
= 37 - 4.5
= 32.5

 Thus, any value lower than 32.5 in this data set is an outlier. Therefore, 32 is an outlier. Since 32 is an outlier, it is plotted outside of the whiskers (i.e. a single dot). The next lowest value that is not an outlier becomes the lower whisker. Since 37 is both Q1 AND the lowest non-outlier in the data set, it becomes the "whisker". Because of this, the whisker is plotted on 37. In this case, you do not have a visual lower whisker. In summation, excel has plotted this data set correctly.

[#7]

Hello @PReagan,

Thanks for your explanation.

But it doesn't solve my problems.

I always need 2 whiskers, not sometimes a oulier / point.

How can I get two whiskers for every situation?

Greetings

Erwin 

[#8]

@ErwinVanLoo,

Can I ask the purpose of always having two whiskers? That might help me understand your problem a little better.

Just to reiterate, the way that a box and whisker plot is intended to be used is such that outliers are plotted beyond Q1 - 1.5*IQR and Q3 + 1.5*IQR. Is this not what you intend to do?

[#9]

Hello @PReagan,

We don't know the rule  Q1 - 1.5*IQR and Q3 + 1.5*IQR you metionned to decide if we would / should draw an outliner instead of a whisker.

We work always with the values minimum, Q1, Q2, Q3 and the maximum value and then we draw the boxplot that is the box (between Q1 - Q3) with the two whiskers included (from min tot Q1 and from Q3 to max).

You can see how we work in the youtube film https://www.youtube.com/watch?reload=9&v=37-1ILIBCOA  (sorry that is is in dutch)

It is possible that a whisker diappear but this is only in the case that the minimum value is equal to Q1 (then the lower whisker is gone) or when Q3 is equal to the maximum value (then the higher whisker is gone).

 We don't know (and don't use) the rule  Q1 - 1.5*IQR and Q3 + 1.5*IQR. to decide if we would draw a whisker or an outliner. We don't know the term outliner. We even don't know the mechanism you mentionned.

Recapitulating, We allways draw a box and two whiskers (except when min value = Q1 or Q3 = maximum value). 

So I was very supprised  to see  sometimes a whisker disappearing / appearing in excel, .... I didn't understand why.

I hope you now understand my problem. I never need an outliner, but always a lower whisker (when minimum value is different from Q1), then a box between the values Q1 - Q2 - Q3 and then the Higer whisker (when Q3 is different from the maximum value)

Greetings

Erwin 

[#10]

You can see at http://www.hhofstede.nl/modules/boxplot.htm that we always have a boxplot and two whiskers even if there is a big difference between the minimum value and q1 and when there is a big difference between Q3 and the maximum value.