Specific Column Reference Based on Value to PERCENTILE

[zpenacho]

Hope my title's not too misleading, I'll try to explain further. I've attached an example workbook for reference.

I need my PERCENTILE formula in column S to take the .25 PERCENTILE of the columns for each "area" defined for the data range in cells B2 and C2. Since B2 is 1/1/2013 I need the first column in the PERCENTILE to be the corresponding 1/1/2013 in column E. Since C2 is 12/1/2013 I need the last column in the PERCENTILE to be the corresponding 12/1/2013 in column P. For Area1 in Row5 it would be =PERCENTILE(E5:P5,.25).

The catch is, I need the formula to be smart enough to change which column it picks based on the value in B2 and C2. If I change my range to 6/1/2013 in B2 and 8/1/2013 in C2 then Area1 needs to be =PERCENTILE(J5:L5,.25).

I asked this question in a different thread trying to keep it simple using SUM instead of PERCENTILE, got a perfect answer w/ SUMIFS, but I really need it w/ PERCENTILE. [For Reference, the SUMIFS answer: =SUMIFS(B5:R5,$B$4:$R$4,">="&$B$2,$B$4:$R$4,"<="&$C$2) ]

If I can give any more detail, please, ask. Thanks for any help!

[bebo021999]

OK, try this:

Please Login or Register  to view this content.

[zpenacho]

Seems to work great, you're the man, thanks bebo! Again, out of curiosity, why the double commas? The ,,?