Limitations of Quartile Function

[dave.mcmanus]

Hi,

I'm trying to reference a dataset in a Quartile formula that is spread
over 2 worksheets, i.e. I have a column of numbers on 2 different
sheets that I want to be evaluated by the same quartile function, but
it's not having it. If it were a Min/Max/Median function I was using
it would have no problem but Quartile only seems to provide an answer
for multiple ranges if the ranges exist on the same spreadsheet.

I can't move my data as this is impractical. Does anyone know of a
solution to this problem?

Thanks,

Dave.

 

[Bernie Deitrick]

Dave,

I can't move my data as this is impractical.

Why can't you? Copy and paste is easy....

Bernie
MS Excel MVP

 

[dave.mcmanus]

Bernie,

unfortunately the data has to stay where it is as there are a whole
series of reports and macros already linked to it and the document is
a key source of information for several users, I can't go moving
things around in it. One worksheet contains monthly commodity prices,
the other contains quarterly commodity prices, they need to stay
separate from each other for ease of interpretation. Also these
sheets are updated daily so I can't just copy the data into another
workbook for the purposes of mashing it together because then I'd have
to update the mashup every day as well (I try to avoid creating
unneccessary work for myself wherever possible!).

So any ideas about how Quartile can use ranges from more than one
worksheet at a time?

Thanks.

Dave.

 

[Bernd P]

Hello,

Use PERCENTILE with a named range ...

Regards,
Bernd

 

[Bernie Deitrick]

Dave,

One way would be to roll your own, with a User-Defined-Function: copy the code below into a regular
module and use it like

=MYQUARTILE(B2:B100,Sheet2!B2:B100,1)

HTH,
Bernie
MS Excel MVP


Function myQuartile(R1 As Range, R2 As Range, Q As Integer)
Dim myArr() As Double
Dim i As Integer
Dim j As Integer
Dim k As Integer

i = R1.Cells.Count
j = R2.Cells.Count

ReDim myArr(1 To i + j)

For k = 1 To i
myArr(k) = R1.Cells(k).Value
Next k

For k = 1 To j
myArr(i + k) = R2.Cells(k).Value
Next k

myQuartile = Application.WorksheetFunction.Quartile(myArr, Q)
End Function

 

[dave.mcmanus]

Hello,

Use PERCENTILE with a named range ...

Regards,
Bernd

Bernd,

PERCENTILE is no good, I have exactly the same problem, the named
ranges must exist on the same worksheet or the formula returns a
#VALUE error.

Thanks,

 

[dave.mcmanus]

Bernie,

you're a star, that's exactly what I needed. I figured that
converting the multiple ranges into a single range would be the way to
go but I just couldn't think where to start.

Many Thanks,

Dave.

 

[dave.mcmanus]

Bernie,

this works perfectly but it presents me with one more problem. The
defined ranges may on occasion contain blank cells where the data has
not yet been entered. I need these cells to be ignored so that even
though they are part of the original range I'd need myArr() to exlcude
them.

Any thoughts on this. I'm not getting very far with it.

Thanks,

Dave.

 

[Bernie Deitrick]

Dave,

Try this (untested, since I need to leave soon):

HTH,
Bernie
MS Excel MVP

Function myQuartile(R1 As Range, R2 As Range, Q As Integer)
Dim myArr() As Double
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim p As Integer

i = R1.Cells.Count
j = R2.Cells.Count

ReDim myArr(1 To i + j)

p = 0

For k = 1 To i
If R1.Cells(k).Value <> "" Then

p = p + 1
myArr(p) = R1.Cells(k).Value
End If
Next k

For k = 1 To j

If R2.Cells(k).Value <> "" Then

p = p + 1
myArr(p) = R2.Cells(k).Value
End If
Next k

Redim Preserve myArr(1 To p)

myQuartile = Application.WorksheetFunction.Quartile(myArr, Q)
End Function

Bernie,

this works perfectly but it presents me with one more problem. The
defined ranges may on occasion contain blank cells where the data has
not yet been entered. I need these cells to be ignored so that even
though they are part of the original range I'd need myArr() to exlcude
them.

Any thoughts on this. I'm not getting very far with it.

Thanks,

Dave.

 

[Bernie Deitrick]

Dave,

I've now had time to test it, and it appears to work.

HTH,
Bernie
MS Excel MVP

 

[dave.mcmanus]

Bernie, thanks again for your help.

Dave.