Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
Hi Dave F
I am trying to find a formula that will give me really accurate compound interest calculations, where interest is calculated daily and added monthly. I have used A=P(1+r) to power n, where A=accumulated sum, P is principal and r=interest rate expressed as a proportion and n is the term. Can you help, please? "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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
Sorry - did not finish this query off - Using the formula below I have been
able to get close to an accurate figure for calculating interest on my mortgage, but it is not quite right for a case where interest is calculated daily and added monthly. Do you have a better suggestion, please? "Formula Fred" wrote: Hi Dave F I am trying to find a formula that will give me really accurate compound interest calculations, where interest is calculated daily and added monthly. I have used A=P(1+r) to power n, where A=accumulated sum, P is principal and r=interest rate expressed as a proportion and n is the term. Can you help, please? "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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
New method for PERCENTILE & QUARTILE formulas
I am looking for a formula to work in Excel 2002/2003 that will give correct
quartile for performance info. where you can have high or low values which are 'good' performance. The standard formulae do not work for both examples. Also what if values are 0? "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
quartile, percentile, and blank cells | Excel Discussion (Misc queries) | |||
Auto Filter and Quartile/Percentile Functions | Excel Discussion (Misc queries) | |||
What is the formula used to calculate the 1st and 3rd quartile? | Excel Worksheet Functions | |||
Quartile / Quintile Function | Excel Worksheet Functions | |||
Quartile Function | Excel Discussion (Misc queries) |