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.