Using PERCENTILE with *specific* cells in a column

Giganews Newsgroups
Subject: Using PERCENTILE with *specific* cells in a column
Posted by:  zodiac711
Date: Wed, 11 Mar 2009

I am trying to use the PERCENTILE function with specific cells in a column.

For example, lets say the column is:
    A
1  5
2  6
3  7
4  8

Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4.

I can achieve this by defining a named range such that it would be equal to
A1 and A4, but would rather not use named ranges, as the above is a very
simplistic example of what I want to do -- using named ranges would require a
substantial amount of effort to achieve my end goal.

I also realize I could re-order the rows such that I swap A4 with A2,
therefore could use percentile on A1:A2, but that will not work for my needs
either.

I attempted to use the INDIRECT function in an array constant, but realized
that you can't use a function in an array constant, so that rules that
thought out.

Is there a way I can "create" the range to be used in an Excel formula (ala
the RANGE function in VBA)?  The named range is doing this (as it works), but
I can't figure out how to emulate it within my Excel workbook.

Thanks for any advice

Replies