Forum Discussion

Copper Contributor

Jul 16, 2020

Excel Box and Whiskers

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

Question for Microsoft.xlsx17 KB

Bronze Contributor

Jul 20, 2020

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!

Copper Contributor

Jul 21, 2020

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

PReagan
Bronze Contributor
Jul 23, 2020
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.
- ErwinVanLoo
  Copper Contributor
  Jul 23, 2020
  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
  - PReagan
    Bronze Contributor
    Jul 23, 2020
    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?

Resources