• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FORMULA - MIN > IF > PERCENTILE

James bolton

James bolton

Member
Hello all,

I am really racking my brain about this formula,#

I am trying to find out the MIN of range, IF the 2nd range matches the specific company & IF the figures are in the top 25 percentile of that range (I've attached a spreadsheet where this makes more sense) I've had to create an extra column to help in the meantime but would really like it as a formula.

I can do the countif variant

Code: 
 =COUNTIFS(B:B,B2,A:A,"<"&PERCENTILE(IF(B:B=B2,A:A),0.25))

Ctrl + Shift + Enter

This Counts how many prices are in the top 25 percentile for Company A (it's basically 25% of the count but the formula "should" follow the same logic for MIN, MAX & AVG)

As always your help is greatly appreciated.
 

Attachments

  • MIN IF PERCENTILE TEST.xls
    20.5 KB · Views: 8
Sorry, knew this was too easy

Forgot to add an extra column

PRICE | DISCOUNT | COMPANY

I need to know the MINimum price IF its for COMPANY A and within the top 25% DISCOUNT range for all things COMPANY A

(So not the top 25% of the whole range, just the top 25% of all that is Company A)

Attached is the updates spreadsheet (P.S. The answer should be I believe £11, based on the breakdown in the 2nd worksheet)
 

Attachments

  • MIN IF PERCENTILE TEST.xls
    26 KB · Views: 6
Chihiro said:
=MIN(IF((C:C=C2)*(B:B>=PERCENTILE.EXC(IF(C:C=C2,B:B),0.75)),A:A))
Click to expand...

If you use entire column references within an array formula then you're forcing Excel to perform an incredible amount of unnecessary calculation.

If, for example, you only have data extending as far as row 1000, then, by referencing an entire column's worth of rows, you are effectively forcing Excel to calculate more than one million rows than are actually necessary, resulting in an astonishingly resource-heavy formula. And that's just for one instance of that formula.

Regards
 
Hi Chihiro - thanks very much thats worked pefectly

& Thanks for the tip XOR LX - that would explain why my sheets have been running so slowly
 
Chihiro said:
Should be...
=MIN(IF((C:C=C2)*(B:B>=PERCENTILE.EXC(IF(C:C=C2,B:B),0.75)),A:A))

Entered as array (CSE)
Click to expand...

Here's a pickle I'm having actually

Now it works if I do this for MIN & MAX, but not for average, I believe the Average should be £28 but it is coming up as £32 ? Don't know where thast number is coming from ?

Code: 
=AVERAGE(IF((C:C=C2)*(B:B>=PERCENTILE(IF(C:C=C2,B:B),0.75)),A:A))

smdgzrK.png
 
@XOR LX
I completely agree with you. I was bit too lazy yesterday :oops:

To avoid using entire column I usually work with named ranges and/or data converted to tables.
 
Hello friends,

Another way, without CSE

=AGGREGATE(15,6,(A2:A50)/((PERCENTILE.EXC(B2:B50,0.75)<=B2:B50)*(C2=C2:C50)),1)

David
 
You must log in or register to reply here.
Back
Top