single-cell array formula with IF and discontiguous cell range

[Johne]

I would like to calculate the 95th percentile on a discontiguous cell range and exclude zeros from the calulation.

I am looking at using a single-cell array formula to accomplish this

I do not want to have to manipulate the source dat if possible - that is I want a formula that can work with a discontiguous cell range

I have experimented with contiguous ranges like this and it works
=PERCENTILE(IF(A1:A10<>0, A1:A10, ""), 0.95)
but when I try various attempts at a discontiguous range - no go!

Please help!

Also, what is a programming language for a newbie to get into working with Excel - would it be VBA? Where to get it and how to get started.

Thanks.

Any feedback would be greatly appreciated.

John

[Miraun]

If the =percentile function doesn't work for a discontinuous range, do you know how percentile is typically calculated outside of the excel function? Personally, I don't know, so I can't help you, but I would recommend breaking down the maths of the percent function and applying those onto the discontinuous range. Like if you need to add in some criteria with average back in 2003, you would need to do a sumif with a countif. Sadly, excel isn't perfect yet. If it were, we wouldn't be able to get milked for more money next version!

Onto your next question, yes, the language is Visual Basic for Applications. From the way that it's referenced on the net, 99% of this application of visual basic is used for excel coding.

As far as learning VBA, I'd recommend using the built in record macro to teach you the basics. Just record something interesting that you find yourself doing, and then see what the recorded macro comes up with. Then like everything else in Excel, play around a little bit, and read some of the forum posts here on the programming folder. You'll see some good (and bad) examples of code to teach you about other coding techniques and stuff that is out there.

[Andrew-R]

A slight modification of your formula to =PERCENTILE(IF(A1:A10<>0,A1:A10),0.95) entered as an array formula (i.e. using Ctrl-Shift-Enter, not just Enter) seems to work fine.