0

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

George
  • 317
  • 2
  • 4
  • 16

1 Answers1

1

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

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

https://i.stack.imgur.com/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

learnAsWeGo
  • 2,252
  • 2
  • 13
  • 19
  • Is it possible with 1 array formula to achieve the answer, instead of creating another column? In reality, I have thousands of rows of data over the course of 24 months, so I'd prefer to do this with 1 formula – George May 09 '18 at 18:30
  • yes just swap the $E$3 with whatever date you are looking – learnAsWeGo May 09 '18 at 18:32
  • right! did that right after I commented :/ thanks for helping! – George May 09 '18 at 18:33