Help with Percentile Array Formula

Hello,

Having trouble getting the formula below to work correctly. I keep getting values of #N/A.

Please Login or Register  to view this content.

(Confirmed with Shift+Control+Enter)

Basically what I want to happen is if cell B7 has a value greater than 6, then I want the 10th Percentile of the values in column I that met both conditions contained in my IF statements. I then want this to continue on for A8, A9, A10 etc.

If I write a separate formula for each cell (A7, A8, A9, etc.) it works perfectly. The trouble is when I attempt to write a single array formula for the entire set A7:A15.

Any help would be greatly appreciated!

Hi Jeffmlucas,

Just try this. "B:B", & "A:A".

Please Login or Register  to view this content.

Thanks
Rohit

That doesn't seem to work. The updated formula actually started putting "*" in more cells than that should be there and the values in the other cells were all the same.

hi Jeffmlucas,

Can you upload a sample worksheet.

Thanks
Rohit

Hello,

I am having an issue with calculating a percentile for a range of cells by using an array. My current formula looks like this:

=IF(B7:B15>6,PERCENTILE(IF(Data!$G:$G=ReportPages!A1,IF(Data!$E:$E=ReportPages!A7:A15,IF(Data!$I:$I>0,Data!$I:$I))),0.1),"*")

I have isolated each part of my formula and discovered that the "ReportPages!A7:A15" part of the formula is causing the issue. If I change this portion so that it only references a single cell, the formula works perfectly. Any help would be appreciated!

Jeff

Hello,

I am having an issue with calculating a percentile for a range of cells by using an array. My current formula looks like this:

=IF(B7:B15>6,PERCENTILE(IF(Data!$G:$G=ReportPages!A1,IF(Data!$E:$E=ReportPages!A7:A15,IF(Data!$I:$I>0,Data!$I:$I))),0.1),"*")

I have isolated each part of my formula and discovered that the "ReportPages!A7:A15" part of the formula is causing the issue. If I change this portion so that it only references a single cell, the formula works perfectly. Any help would be appreciated!

Jeff