percentile value when input changes

[alanyaber]

Hi
In the attached sheet I have two inputs and I'd like calculate the percentile value for a range of data, copy the values in that range before moving to the next set of data.

Is there a formula to help me do this please?

[Bryan Hessey]

Hi
In the attached sheet I have two inputs and I'd like calculate the percentile value for a range of data, copy the values in that range before moving to the next set of data.

Is there a formula to help me do this please?

Hi,

try

=PERCENTILE(IF(A$5:A$65536=A5,B$5:B$65536),0.85)

and formula fill that as required

note, this is an array formula and requires CTRL/Shift/Enter (not just simple Enter)

yoiu might like to add

=IF(A5="","",PERCENTILE(IF(A$5:A$65536=A5,B$5:B$65536),0.85))

hth
---

[alanyaber]

You are simply a genius-----thank you so much

[Bryan Hessey]

You are simply a genius-----thank you so much

would that I were, but good to see that it works for you, and thanks for the response.

---

[alanyaber]

Okay--- this is taking long time to process the data..is this normal??

[Bryan Hessey]

Okay--- this is taking long time to process the data..is this normal??

I guess that would depend upon what you meant by 'processs the data' and how much data you have.

for a 10,000 row set a couple of seconds or so.

What bit of 'process the data' are you referring to?

---