Results 1 to 7 of 7

20081022, 02:23 #1
 Join Date
 Oct 2008
 Location
 Bury St Edmunds
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Adding a PERCENTILE function to a formula (2003)
I need to add a PERCENTILE function to this already complicated formula. The whole thing has tied me up in knots...im going to be adding an upper percentile as well, but for now im just trying to do the lower percentile of 5%!! Im pretty sure most of the formula stays the same, but its just where do i add the function and how many brackets do i need to put in!!!
=IF(ISERROR(AVERAGE(IF((Data!$E$2:$E$5001=$Q65)*(L EFT(Data!$G$2:$G$5001,3)=LEFT($R78,3)),((Data!$I$2 :$I$5001))))),"",(AVERAGE(IF((Data!$E$2:$E$5001=$Q65)*(LEFT(Data! $G$2:$G$5001,3)=LEFT($R78,3)),((Data!$I$2:$I$5001) )))))

20081022, 02:44 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Adding a PERCENTILE function to a formula (2003)
Try
=IF(ISERROR(PERCENTILE(IF((Data!$E$2:$E$5001=$Q65) *(LEFT(Data!$G$2:$G$5001,3)=LEFT($R78,3)),Data!$I$ 2:$I$5001),5%)),"",PERCENTILE(IF((Data!$E$2:$E$5001=$Q65)*(LEFT(Dat a!$G$2:$G$5001,3)=LEFT($R78,3)),Data!$I$2:$I$5001) ,5%))
As you're no doubt aware, this is an array function, i.e. you should confirm it with Ctrl+Shift+Enter instead of just Enter.

20081022, 03:44 #3
 Join Date
 Oct 2008
 Location
 Bury St Edmunds
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding a PERCENTILE function to a formula (2003)
Hi Hans,
I think im being really stupid, but i think ive spent too much time on this and it has done my head in. I have done what you said, but im not convinced of the results. I have inlcuded a proper version of what im trying to achieve. I am trying to get the lower percentile of 5% of my average waits and the upper quartile of 95%. Im not sure what format to have the numbers either  whether to have them as %s or as times. Is there something you suggest?

20081022, 04:05 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Adding a PERCENTILE function to a formula (2003)
The results are OK, but you have set calculation to manual in your workbook, so the formulas aren't updated automatically when you enter a different number in A2.
The results of the PERCENTILE formulas should be formatted as times, just like the results of the AVERAGE formulas. If the 5% percentile is 1:01 and the 95% percentile is 3:32, it means that 5% of the waiting times are shorter than 1:01, 5% are longer than 3:32, and the remaining 90% are in between.

20081022, 04:13 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Adding a PERCENTILE function to a formula (2003)
BTW, as far as I can tell, there is no need to use LEFT(..., 3) in the formulas. Since you're calculating array formulas over large ranges, the use of LEFT slows down recalculation considerably.
The attached version contains simplified versions without LEFT and minus some superfluous parentheses.

20081022, 04:21 #6
 Join Date
 Oct 2008
 Location
 Bury St Edmunds
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Adding a PERCENTILE function to a formula (2003)
Hans, thank you  you have been a great help today and i have learned loads. Hopefully I wont bother you too much in future!

20081022, 04:25 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Adding a PERCENTILE function to a formula (2003)
Your're welcome. Don't hesitate to ask more questions if necessary  that's what we're here for!