Forum Discussion
Excel Box and Whiskers
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
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!
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
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.5Thus, 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.
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