percentile - data in 2 different ranges

[Idoia]

I need to calculate a percentile, but my data is not organized as one single
array, but in 2 ranges.

I've tried things like:
=+PERCENTILE({A1:A4,D811},0.9)
=+PERCENTILE({A1:A4;D811},0.9)
=+PERCENTILE(A1:A4&D811,0.9)
={+PERCENTILE(A1:A4&D811,0.9)}

Nothing seems to work... Any ideas?

Thanks

 

[Domenic]

I need to calculate a percentile, but my data is not organized as one single
array, but in 2 ranges.

I've tried things like:
=+PERCENTILE({A1:A4,D811},0.9)
=+PERCENTILE({A1:A4;D811},0.9)
=+PERCENTILE(A1:A4&D811,0.9)
={+PERCENTILE(A1:A4&D811,0.9)}

Nothing seems to work... Any ideas?

Thanks

Try...

=PERCENTILE((A1:A4,D811),0.9)

 

[Idoia]

Thanks, I seem to have tried everything but the easy way...

Now a bit more difficult: what if my range is in a different sheet in the
same book? The ranges are located in a different place in each sheet, so 3D
references won't work?

Your formula now becomes
=+PERCENTILE((A1:A4,Sheet2!D811),0.9)

But stops working

Thanks a lot

 

[David Biddulph]

Someone else can hopefully answer your question, but what I can tell you is
that you don't need the + signs. A Excel formula starts with =, not with
=+. The + is a relic from old Lotus spreadsheets.

 

[Domenic]

Thanks, I seem to have tried everything but the easy way...

Now a bit more difficult: what if my range is in a different sheet in the
same book? The ranges are located in a different place in each sheet, so 3D
references won't work?

Your formula now becomes
=+PERCENTILE((A1:A4,Sheet2!D811),0.9)

But stops working

Thanks a lot

If each range references a single column and the ranges are the same
size, try...

=PERCENTILE(CHOOSE({1,2},A1:A4,Sheet2!D811),0.9)