Percentile with multiple variable ranges

**Michael Maor** · 04-22-2018, 10:29 AM

Hello,

I need to calculate various parameters including the 70th percentile of a number of partial data sets based on values set in other cells in the sheet. I am using OFFSET to set the data range, and was planning on using PERCENTILE to calculate the 70th percentile, however percentile does not appear able to handle multiple ranges in different sheets. Is there a way to use PERCENTILE formula, or some other formula to do this?

With a single range it works fine and with two static ranges in the same sheet it works, however when I try and add variable ranges in separate pages I get the error “A value used in the formula is of the wrong data type.” I'm using Excel 2007, is there anyway to get this to work?

I was going to include examples of the formulas that worked and those that didn't, but they seem to be parsed as links which I can't include in my posts yet.

**Glenn Kennedy** · 04-22-2018, 11:31 AM

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Michael Maor** · 04-23-2018, 01:08 AM

Thanks for explaining how to attach files, I thought this was something I was blocked from doing. I prepared a sample file with two ranges, now attached.

**FlameRetired** · 04-23-2018, 01:18 AM

Hi Michael Maor. Welcome to the forum.

Try

Formula:

Please Login or Register  to view this content.

I get -0.51 which I believe is correct. The two ranges are identical.

Edit I noticed that if the 'Start' value in A9 is changed to 3 the formula will return -0.6

Percentile with multiple variable ranges

LinkBack

Thread Tools

Rate This Thread

Display

Percentile with multiple variable ranges

Re: Percentile with multiple variable ranges

Re: Percentile with multiple variable ranges

Re: Percentile with multiple variable ranges

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Selecting multiple ranges using variable

Percentile ranges

Percentile ranges - keeping data & ranges seprately in different tabs

Finding a Variable Value with in multiple ranges

Creating multiple charts with variable ranges

[SOLVED] Counting variable ranges and auto-summing variable ranges

[SOLVED] A range variable consisting of multiple ranges.

Bookmarks

Bookmarks

Posting Permissions