Methods for Quartile Calculation with Multiple Criteria

[Sangar_338]

I need to calculate quartiles on the assumption that 5 criteria are met but I am unable to determine which method is logically the most sound.

From what I can think of, there are various ways to do this: Using the IF function or using an IFS function.

(1) One way seems to be to nest IF functions, and there seem to be two ways to do this.

There seem two ways to do this.

(1.1) Example, calculating Quartile 1 with two criteria:

=QUARTILE(IF(($A$2:$A$100=E2),IF(($B$2:$B$100=F2),$C$2:$C$100),""),1)

Note: It works with 2 criteria.

(1.2) Example, calculating Quartile 1 with 5 criteria:

=QUARTILE(IF(AND($A$2:$A$100=E2,$B$2:$B$100=F2,$C$2:$C$100=G2,$D$2:$D$100=H2,$E$2:$E$100=I2),$C$2:$C$100),1)

Note: I can't get this one to work. Might the logic be flawed?

(2) The other way seems to be to use an IFS function, although I am as of yet unsure about what the logic/formula would look like.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Sangar_338]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, I have made the update to my profile to show that I'm using Microsoft® Excel® for Microsoft 365 MSO. Appreciate the tip.

 

[Fluff]

Thanks for that.
How about

=QUARTILE(FILTER($C$2:$C$100,($A$2:$A$100=E2)*($B$2:$B$100=F2)*($C$2:$C$100=G2)*($D$2:$D$100=H2)*($E$2:$E$100=I2)),1)

Although I don't understand why you are limiting col C to the value in G2, when that is the column your using for the quartile

 

[Sangar_338]

Thanks for that.
How about

=QUARTILE(FILTER($C$2:$C$100,($A$2:$A$100=E2)*($B$2:$B$100=F2)*($C$2:$C$100=G2)*($D$2:$D$100=H2)*($E$2:$E$100=I2)),1)

Although I don't understand why you are limiting col C to the value in G2, when that is the column your using for the quartile

Thank you. I had to go and read up on the FILTER function, as I had not used it before. Your proposed solution is elegant and easy to understand. Much appreciated.

Also, your comment regarding Column C made me realise that I indeed made an error: Column C is not a column that contains criteria data.

Therefore, I have corrected this in the following formula, after making changes in source sheet, so that the entry in row 1 of each of the criteria data columns are actual criteria that require referencing:

=QUARTILE(FILTER($C$2:$C$100,($A$2:$A$100=A1)*($B$2:$B$100=B1)*($F$2:$F$100=F1)*($D$2:$D$100=D1)*($E$2:$E$100=E1)),1)

 

[Fluff]

You're welcome & thanks for the feedback.