Quartile with If function

[JVBro]

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.

 

[GraH - Guido]

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.

 

[p45cal]

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)

 

[GraH - Guido]

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))