1. ## 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) )))))

2. ## 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.

3. ## 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?

4. ## 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.

5. ## 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.

6. ## 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!

7. ## 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!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•