Re:Is Microsoft Excel an Adequate Statistics Package?
by hexane360 on Saturday August 27, 2016 @09:17PM
(#12374874)
I've spent a lot of time using Excel in ways it was never meant for.
If anyone is trapped using Excel to make a box plot, it is possible (disclamer please for the love of god never do this): Make a stacked column chart. The first series should be equal to Q1 minus the graph minimum. Format this to be invisible. Series two should be median - Q1, formatted as a black outline on a white bar. Series three should be Q3 - median, formatted like series two. Error bars can be added to give the whiskers. Here's an example: https://i.ytimg.com/vi/ucWmfmXb1kk/maxresdefault.jpg
Again, don't do this.
For a histogram, just make a min and max for each bin, and use countifs(range, ">=" & min, range, "<" & max) to get the amount in each bin.
If anyone is trapped using Excel to make a box plot, it is possible (disclamer please for the love of god never do this): Make a stacked column chart. The first series should be equal to Q1 minus the graph minimum. Format this to be invisible. Series two should be median - Q1, formatted as a black outline on a white bar. Series three should be Q3 - median, formatted like series two. Error bars can be added to give the whiskers. Here's an example: https://i.ytimg.com/vi/ucWmfmXb1kk/maxresdefault.jpg
Again, don't do this.
For a histogram, just make a min and max for each bin, and use countifs(range, ">=" & min, range, "<" & max) to get the amount in each bin.