Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Percentile Array Formula (Excel 2003 SP 1)

    Hello once again to all you Excel gurus. I'm back with another challenge (at least it's a challenge for me). I have the following formula:

    =SUM(IF(A3:A800=A1,IF(B3:B800>=B1,IF(B3:B800<=C1,I F(NOT(ISERROR(C3:C800)),C3:C800,0),0),0)))

    which works fine. It returns a value based on the conditions in the formula.

    The problem is that I need to actually add not ALL the cells in C3:C800 based on the conditions; instead I need to add the upper quartile (top 25%) of those cells. And to complicate it a little further, some of the cells in C3:C800 contain #VALUE errors, while others contain "NM", while others contain values, which is why the IF(NOT(ISERROR... construction.

    I would have posted a sample, but I can't seem to get the file size below 100K with the formula in it.

    I feel like I'm so close but I just can't get it....

    Thanks in advance, once again...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Percentile Array Formula (Excel 2003 SP 1)

    Enter the following array formula in cell D1:

    =PERCENTILE(IF(NOT(ISERROR(C3:C800)),IF(NOT(ISBLAN K(C3:C800)),C3:C800,"")),0.75)

    Use the following array formula to add the numbers in C3:C800 satisfying all the conditions:

    =SUM((A3:A800=A1)*(B3:B800>=B1)*(B3:B800<=C1)*IF(N OT(ISERROR(C3:C800)),IF(ISNUMBER(C3:C800),IF(C3:C8 00>D1,C3:C800))))

    Depending on what exactly you want, you may want to change the > in C3:C800>D1 to >= (greater than or equal to).

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Percentile Array Formula (Excel 2003 SP 1)

    Hi Hans,

    Good suggestion. I am not in front of that file right now, but I will try your suggestion first thing in the morning.

    I was hitting a wall with this formula. I finally had to give up and go to the pros...

    I'll let you know how it works...

    Thanks again...

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Percentile Array Formula (Excel 2003 SP 1)

    Hi Hans,

    Well, I just tried your combination and it works. Next I decided to put the two formulas together and I got that to work as well...

Posting Permissions

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