Excel Candlestick Plot, quartiles not working.

Question

Excel Candlestick Plot, quartiles not working.

Mia 0

Hello,

For a school assignment I must construct a candlestick/box and whisker graph, including the quartile labels.

I made it using the following values:

-4

0

6.5

0.5

-10.5

-7

-4.5

-14

-10.5

-15

I was very distressed to find, even after making sure that an 'inclusive median' was calculated, the Q3 value is still displayed incorrectly. The graph says the value is -1, even though I know that Q3 is zero from my own and third party determinations. How do I resolve this issue with my graph? The other values are correct.

0 comments

1 answer

Your answer

Answer 1

Hendrix-C 16,725 Microsoft External Staff Moderator

Hi @Mia,

Based on your sharing, I think this is a quartile-definition mismatch. For your data set, if you expected the result Q3=0, you should use Exclusive median (to exclude the overall median when n is odd), not Inclusive median.

This is my calculation in both Exclusive and Inclusive median:

Exclusive median

User's image

Inclusive median

User's image

Hope my sharing may help with your concern. If you have any questions or need further clarification, please feel free to leave a comment here and I’ll be happy to continue assisting you.

Thank you for your patience and understanding. Hope to hear from you soon.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Mia 0 Reputation points

2026-04-28T16:20:59.4566667+00:00

Hi,

Unfortunately I tried this already, and when my median was exclusive ALL of my quartiles were incorrect. I'm also sorry to say that there was an error in copy/pasting my data set. There is only one -4 in my population, so there are only 10 data values.
Hendrix-C 16,725 Reputation points Microsoft External Staff Moderator

2026-04-28T17:50:53.7666667+00:00
Hi @Mia,

Thank you for your prompt update.

In that case, may I confirm what is the quartiles method that your school assignment uses? Is it using a "split the sorted list into two halves and take the median of each half"?

If it's true, the difference in your expected outcome and Excel calculation is understandable since Excel uses a different quartile method. By design, Excel QUARTER.INC returns quartiles based on percentile values from 0 to 1 inclusive. Let me clearly explain it logic:

When you use QUARTILE.INC(array, 3), it means return the Q3 or 75th percentile. Also, the QUARTILE.INC is the quartile version of PERCENTAGE.INC, which will interpolate when the percentile does not land exactly on one ranked position.

The percentile position formula is rank = 1 + (n-1)*k where n is number of data points and k is decimal percentile

Since QUARTILE.INC(array, 3) is the 75th percentile > n = 10 and k = 0.75 > rank = 1+(10-1)*0.75 = 7.75 > Excel will look for the value at position 7.75

With your sorted data, 7.75 lies between position 7 is -4 and position 8 is 0 > Excel will interpolate between -4 and 0: Q3 = -4 + 0.75*[0 - (-4)] = -4 + 0.75*4 = -4 + 3 = -1 > QUARTILE.INC(array, 3) will return -1

It uses the same logic with Q1 which means the QUARTILE.INC(array,1) will look for position 3.25. However, since position 3 and 4 are luckily both the same -10.5, the formula result is also as -10.5. You can test by changing the position 4 to a slightly different number like -10.25, the Q1 result will change.

In conclusion, it maybe not because Excel calculates wrong but there is a difference in calculation method that you are using compared to Excel logic. Please confirm which method you use for quartile calculation, and I will help you with the next step to create a correct Box & Whisker for your assignment.

Thank you for your patience and understanding. Looking forward to your response.
Hendrix-C 16,725 Reputation points Microsoft External Staff Moderator

2026-04-29T22:44:44.88+00:00

Good day @Mia,

Hope things are running smoothly on your end.

Following up on the support thread we've been working on. If you need assistance with anything else, kindly respond to this post, and I will be happy to help.

I want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding throughout the troubleshooting process.

I look forward to hearing from you soon.
Hendrix-C 16,725 Reputation points Microsoft External Staff Moderator

2026-05-01T22:09:16.2733333+00:00

Hi @Mia,

I hope you are having a great day.

I'm reaching out again to follow up on your issue and check if everything has been resolved. My goal is to ensure your experience remains smooth and hassle-free. If you're still encountering any problems or have run into new challenges, please let me know which steps you're currently stuck on and happy to provide further help whenever you need it.

If you have any further questions, feel free to tag me in your reply so I can assist you directly.

Looking forward to your update.
Mia 0 Reputation points

2026-05-04T15:03:30.85+00:00

Hi,

No, my issue was not resolved but the assignment due date approached, so I had to manually make the graph.
Hendrix-C 16,725 Reputation points Microsoft External Staff Moderator

2026-05-04T19:53:22.4366667+00:00

Hi @Mia,

I'm really sorry that we can't find the way to resolve the issue effectively before the due date of your assignment.

May I ask whether you have checked my second response for the issue yet? Although we cannot make it on time initially, I want to help clearly identify the root cause so you can prevent the issue from happening again in the future.

Thank you for your cooperation and understanding during this process. Hope to hear from you soon.
Hendrix-C 16,725 Reputation points Microsoft External Staff Moderator

2026-05-06T22:25:41.79+00:00

Hi @Mia,

I hope you are doing well.

It's been great working with you on this issue. I understand you may have other priorities right now, and that’s completely fine. If you’re still facing any issues or new challenges, let me know where you’re stuck. I’m happy to help whenever it’s convenient for you.

Thank you for your time and collaboration. I look forward to hearing from you when you’re ready.

Warm regards.

Share via

Excel Candlestick Plot, quartiles not working.

1 answer

Your answer