CODEWITHSUNDEEP
excelfunctionpercentile
user2395238
user2395238

Reputation: 900

Dynamic column for Percentile function in Excel

I have a data set with many columns. I need to calculate percentiles for each one of the column into a template that is arranged in a way that if cannot just copy over the formula. Is there a way to make the column from which I calculate the percentile dynamic? See the table below for a simplified example. What I am trying to achieve is a formula that would look something like this: =percentile.inc(column array with name in $A11,0.25)

This formula would be written into cell B11 and it will calculate 25th percentile of payment column. When I copy this formula to B12 it will automatically calculate percentile of distance.

enter image description here

Upvotes: 0

Views: 578

Answers (1)

BigBen
BigBen

Reputation: 50008

Using INDEX/MATCH:

=PERCENTILE.INC(INDEX($B$2:$C$7,,MATCH(A11,$B$1:$C$1,0)),0.25)

enter image description here

Upvotes: 2

Related Questions