CODEWITHSUNDEEP
excelexcel-formula
Doopster
Doopster

Reputation: 3

Finding the Quartile of an array with multiple criteria in Excel

I am trying to find the 1st and 3rd quartiles from an array that is derived from the data with a set criteria.

For example:

Apple   red     dog   10
Apple   blue    cat   5
Banana  blue    dog   6
Banana  blue    cat   4
Apple   red     dog   8
Apple   red     dog   
Apple   red     dog   1

The formula that I am using and stuck on is

=QUARTILE.INC(SMALL(IF((A1:A7="Apple")*(C1:C7="dog")*(B1:B7="red"),D1:D7),ROW(INDIRECT("1:"&COUNTIFS(A1:A7,"Apple",C1:C7,"dog",B1:B7,"red")))),1)

The answer that is returned is 0.75 where it should really result in 4.5. To me it seems like excel is grabbing empty cells and including it in the derived array, is there a way to remove these empty cells from the array?

Upvotes: 0

Views: 1192

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

I prefer the AGGREGATE() function to do this with IF():

=AGGREGATE(17,6,D1:D7/((A1:A7="Apple")*(C1:C7="dog")*(B1:B7="red")*(D1:D7<>"")),1)

I added the check to skip the blank value also.

The last digit is the quartile desired. Change the 1 to 3 for the third.

enter image description here

Upvotes: 1

Related Questions