Formula to filter out the year based on a list of dates

I have array entered formulas* for MAX, AVERAGE and MIN.
For MAX use: =MAX(IF(YEAR(A$2:A$165)=E5,B$2:B$165))
For AVERAGE use: =AVERAGE(IF(YEAR(A$2:A$165)=E5,B$2:B$165))
For MIN use: =MIN(IF(YEAR(A$2:A$165)=E5,B$2:B$165))
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I will have to come back to the 95%ile later, if someone has not already given you a solution by then.
I'll also add that your pivot table on sheet 2 seems work if you group your dates by year.
Let us know if you have any questions.

For 95%ile calculation try the following:
1) Select cell I5,
2) Paste the following array entered formula into the formula bar:

Formula:

Please Login or Register  to view this content.

3) Simultaneously press the Ctrl, Shift and Enter keys,
4) drag the fill handle down to I11.
Let us know if you have any questions.

As to the dates, pick out one and right click the mouse, then choose group, selecting year and deselecting month.
As to the formula try:

Formula:

Please Login or Register  to view this content.

As to using a pivot table, yes. I have set up a pivot table on sheet 2 that uses location and determinant as report filters. I believe that a pivot table will be your best option with a file the size of the one attached to post #5. I will have to do some research on finding out how/if it is possible to obtain the 95 percentile on the pivot table, unless of course someone else solves that first.

I am going to have to admit failure with obtaining the 95th percentile using the loginv function for the file attached to post #5.
As to the file attached to post #5 the file it is too large for me to upload so I will have to explain as much as I was able to do:
1) It appears that the values in column B are text not dates - Notice that about row 1154 the value is 13/1/2012 - I used the text to columns feature on the data tab to change this.
a) I selected B2 and pressed Ctrl and the down arrow to select the column,
b) I opened the text to columns feature,
c) I selected 'Next' twice then selected Date: DMY and 'Finish'
2) I placed the following array entered formula in I2: =MAX(IF(YEAR(B$2:B$194000)=H2,D$2:D$194000))
3) After simultaneously pressing the Ctrl, Shift and Enter keys I dragged the fill handle down to I7
4) I repeated steps 2 and 3 for the average column using the array entered formula: =AVERAGE(IF(YEAR(B$2:B$194000)=H2,D$2:D$194000))
and with the MIN column using: =MIN(IF(YEAR(B$2:B$194000)=H2,D$2:D$194000))
As I said I failed to get a 95%ile using LOGINV so I used the array formula: =PERCENTILE.INC(IF(YEAR(B$2:B$194000)=H2,D$2:D$194000),0.95)
I will ask the community for assistance.

As for the file attached to posts #4 and #6 I was able to get use the following LOGINV based array entered formula to obtain a 95th percentile:

Formula:

Please Login or Register  to view this content.

I was not successful in finding a way to get the pivot table on sheet2 to display a 95th percentile field.

What number do you expect - for 2011 I get 17.77477 as the 95th percentile for a normal distribution?

15000 rows is not a sample. If it's cut down to size, then it's feasible to see what's going wrong and correct it.

Adjust the formulae to ignore the error. For example the MAX:

=MAX(IF(NOT(ISERROR($E$2:$E$21)),IF(YEAR($A$2:$A$21)=$F2,$E$2:$E$21,"")))

array entered.


See sheet. How does this look?