Difference in quartile values in charts and formulas

**Mayasak** · 03-08-2022, 04:14 PM

Hi,

I keep getting different values when I run the quartile formula and the box and whisker chart. Is this how it's supposed to be. If so how can we get a chart with the same numbers as the ones in the chart? I attached a sample

**Bernie Deitrick** · 03-08-2022, 05:38 PM

Format the data series and use "Inclusive Median" as the option:

Capture.JPG

**MrShorty** · 03-08-2022, 05:41 PM

My older version of Excel does not support the specialty box and whisker diagram, so I cannot see what the chart is giving for your quartile values. I notice that your formula in B2:B3 is the older QUARTILE() function (equivalent to the newer QUARTILE.INC() function). I also know that the chart allows you to choose between "inclusive" quartiles (QUARTILE.INC() function equivalent) and "exclusive" quartiles (QUARTILE.EXC() function equivalent). My first guess as to why your QUARTILE() formula and the chart are giving different results is that you are using "exclusive" quartiles in the chart. If this is the case, then the solution is to properly choose which "quartile" algorithm you want to use (inclusive or exclusive) and make sure both the spreadsheet formula and the chart are using the same algorithm.

Edit: I guess i was just a bit too slow, Bernie seems to have seen the same thing I was guessing at.