Exclude cell from an array

Question

Hi,

I am having some trouble with a calculation. I appreciate all the help.

The issue is regarding calculation of Quartile or Percentile of an array excluding one value.

For example, I have 100 random values between 0 and 1; =RAND(), in A1:A100. What I'd like to do is calculate first quartile in B1 of array A1:A100 excluding cell A1 and then have the ability to drag down all the way to B100. So, in B25, I would like the value of first quartile of array A1:A100 excluding cell A25.

That way, I will end up with 100 first quartile values and each value will have a unique cell excluded from the sample. Averaging the 100 values will provide me with a non-biased first quartile.

Cheers,

H

Answer 1

Hi

Example with A1:A20 and a duplicate (my dec. separator is the comma):

in B1:

=AGGREGATE(17,6,1/(ROW(A$1:A$20)<>ROW(A1))*(A$1:A$20),1)

The above formula only exclude the value in column A on the same row. So, when in B7 the 0.550135 in A7 is excluded but not its duplicate in A10

If you want to exclude the duplicates the formula is less complex:

=AGGREGATE(17,6,1/(A$1:A$20<>A1)*A$1:A$20,1)

Answer 2

Thank you so much, Lz! This is perfect and worked like a charm.

Cheers

Answer 3

Hi H

Have you checked the above proposals. Does one do what you want?