Francisco's range had 10 elements, not 11. [If he had indeed had 11
elements, from 0 to 100 instead of from 10 to 100, then his upper quartile
would indeed have been at 75.]
Another flaw in your arithmetic, Dave, is that (100*3/4)+2.75 = 77.75, not
77.5.
One way of looking at it is as follows:
If there are n elements, numbered from 1 to n, then the 0th percentile is at
position 1 (value 10), and the 100th percentile is at position n (at 100).
The median (50th percentile) is at position (n+1)/2, hence at 55.
The upper quartile (75th percentile) is at position (3n+1)/4, hence at 77.5.
.... but there are a number of alternative methods.
--
David Biddulph
"Dave F" wrote in message
...
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
"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