How to annotate a boxplot in ggplot2

Problem

We want to annotate a boxplot in ggplot2. In our example we will add Tukey's five number summary (minimum, lower-hinge, median, upper-hinge, and maximum).

fivenum(iris$Sepal.Width)

[1] 2.0 2.8 3.0 3.3 4.4

library(ggplot2)
ggplot(iris, aes(factor(0), Sepal.Width)) + 
geom_boxplot() + xlab("") + scale_x_discrete(breaks = NULL)

Solution

We store Tukey's five number summary as a data frame, and then pass it as the argument data inside geom_text to add text to the plot.

five <- data.frame(x = rep(1, 5), five = fivenum(iris$Sepal.Width))
ggplot(iris, aes(x = 0, y = Sepal.Width)) + 
  geom_boxplot() + xlab("") + scale_x_discrete(breaks = NULL) + 
  geom_text(data = five, aes(x = 0, y = five, label = five), nudge_x = 0.5)

References

• geom_text
• geom_boxplot

Return the sum of multiple columns with SUMPRODUCT

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. We use the function SUMPRODUCT with multiple AND (asterisk: *) and OR (plus: +) criteria.

=SUMPRODUCT((B2:M4)*(A2:A4=B7)*
      ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11)))

First we pass the range we want to sum (B2:M4), and then add the conditions: 1) Year 1 (A2:A4=B7); and 2) Months to sum: ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11))). In English, the year must be equal to Year 1 and the months must be January or February or April or December.

Related posts

• Return the sum of multiple columns with VLOOKUP

Return the sum of multiple columns with VLOOKUP

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. With VLOOKUP we pass within the third argument col_index_num the array of columns we'd like to sum: {2,3,5,13}, always enclosed in curly brackets. This will return an array of 4 values {98,52,75,60}. :


{=VLOOKUP(B7,A1:M4,{2,3,5,13},0)}

2. To sum the elements of the array, we use the function sum and then press CTRL + SHIFT + ENTER or we can use the function SUMPRODUCT that doesn't require CTRL + SHIFT + ENTER.

{=SUM(VLOOKUP(B7,A1:M4,{2,3,5,13},0))}
=SUMPRODUCT(VLOOKUP(B7,A1:M4,{2,3,5,13},0))

3. An different approach would be to use SUMPRODUCT with multiple OR criteria described in this post.

Related posts

• Return the sum of multiple columns with SUMPRODUCT