Results 1 to 7 of 7
  1. #1
    New Lounger
    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) )))))

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

  3. #3
    New Lounger
    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?
    Attached Files Attached Files

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

  5. #5
    Plutonium Lounger
    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.
    Attached Files Attached Files

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

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

Posting Permissions

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