I don't think you understand the concept of quartiles correctly. Quartiles
are NOT quarters. A quartile is any of the three values which divide the
sorted data set into four equal parts, so that each part represents 1/4th of
the sample or population.
Your range has eleven elements; accordingly 11/4 = 2.75. Thus the third
quartile is equal to (100*3/4)+2.75 or 77.5.
Neither MEDIAN nor QUARTILE calculates incorrectly; you just don't seem to
understand the terms.
Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.
"Francisco" wrote:
Dave,
That is not entire true, there are 2 methods. That is fine for certain
cases, however when it is necessary to take a value that belongs to the
series this method is not appropriated. The key is: "it has to belong". Excel
should allow me to fine tune this.
I have another example where Excel has the WRONG calculation:
{10,20,30,40,50,60,70,80,90,100}
What do you expect if you do =quartile(RANGE,3), Upper Quartile?
Yes, you will expect 75 as the Upper Quartile .... but surprise surprise
...Excel shows 77.5.
This is just plain wrong.
What I am saying is to extend NOT to replace the funcionality to have a more
appropiate calculation.
F
"Dave F" wrote:
50255 is the median of the sequence of numbers you give. A set of six values
does not have a median exactly equal to the value of any of the numbers.
Rather, it is equal to the third largest value plus the fourth largest value
divided by two.
Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.
"Francisco" wrote:
I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.
We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.
"BoniM" wrote:
=INT(PERCENTILE(A1:A10,0.5))
"Francisco" wrote:
I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.
My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.
Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/comm...lic.excel.misc