CODEWITHSUNDEEP
excelif-statementquartile
George
George

Reputation: 347

Find quartile of range based on value in other column

I have the following data in excel starting in cell A1. I want to find the 25th percentile (quartile 1) of the "Time" data based on the Month. For example, what is the 1 quartile of the time data in Apr-17? I wrote this formula, but it did not work.

What formula can one write to answer the question posed above?

=IF(Month = Apr-17, QUARTILE(B2:B9,1),"")

enter image description here

Upvotes: 0

Views: 1933

Answers (1)

learnAsWeGo
learnAsWeGo

Reputation: 2282

Be sure to set as an array formula with ALT-SHIFT-ENTER

=QUARTILE.INC(IF(A2:A10=$E$3,B2:B10,""),1)

https://i.sstatic.net/1v8h4.png

I like to break these things up into single steps before combining into a larger formula. In column C I have 

=IF(A8=$E$8,B8,"")

Then in E5 I have 

=QUARTILE.INC(C2:C10,1)

From there I was able to put together a formula that works.

Also note that I wasn't 100% sure how your date looks, so I used a input month cell. If you prefer you can replace $E$8 with the date

EDIT:

Or even replace with a date(year,month,day) 

=IFERROR(QUARTILE.INC(IF(A2:A10=DATE(2018,4,1),B2:B10,""),1),"NONE FOUND")

Again remember to set as an array formula with ALT SHIFT ENTER

Upvotes: 1

Related Questions