• 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.

Quartile with If function

JVBro

New Member
Dear all

I want to calculate the quartile of the global appreciation score (C2:C48 = column of Table319 which I named "globale") per workshop.
I named A2:A48 = column of Table319 "workshop". Table319 is on the sheet: "Data workshops - Make-over".

For example for the first quartile: {=QUARTILE(IF(workshop=$D5,globale),1)}

The formula gave the correct results for ONLY 2 of the 4 workhshops (see attachment).

Can someone please help me to correct this?

Thank you in advance

J.
 

Attachments

  • Question Quartile and Median If.xlsx
    23 KB · Views: 3
Not sure I understand how you arrive at your manual results.
I've used the aggregate function to replace your formula:
=AGGREGATE(19,6,Tabel319[[Globale appreciatie]:[Globale appreciatie]]/(Tabel319[[Workshop]:[Workshop]]='Grafieken - Make-over'!$D3),COLUMN(A$1))

drag down and across. Some results have improved.
 

Attachments

  • Copy of Question Quartile and Median If.xlsx
    21.9 KB · Views: 7
When you have blanks in the globale column the quartile function is taking them as zero. We get the desired results if the blanks/zeroes are treated as FALSE, so:
=QUARTILE(IF(workshop=$D5,IF(NOT(ISBLANK(globale)),globale)),1)
(array-entered as you've already done)
 
With this insight from @p45cal ,
my non-CSE formula becomes
=AGGREGATE(19,6,Tabel319[[Globale appreciatie]:[Globale appreciatie]]/((Tabel319[[Workshop]:[Workshop]]='Grafieken - Make-over'!$D3)*(Tabel319[[Globale appreciatie]:[Globale appreciatie]]<>"")),COLUMN(A1))
 
Back
Top