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.

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@gmail.com> wrote in message
news:4d4798ed-5450-4e9a-bec3-07dddddad1f4@m45g2000hsb.googlegroups.com...
> 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,

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.

Hello,

Use PERCENTILE with a named range ...

Regards,
Bernd

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@gmail.com> wrote in message
news:47fffde8-f7ef-41c0-9b44-7aec4deb44c5@k37g2000hsf.googlegroups.com...
>
>
> 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.
>
>
>

On 7 Jul, 17:58, Bernd P <bplumh...@gmail.com> wrote:
> 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,

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.

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.



On 7 Jul, 18:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> Dave,
>
> One way would be to roll your own, with a User-Defined-Function: copy thecode 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.mcma...@gmail.com> wrote in message
>
> news:47fffde8-f7ef-41c0-9b44-7aec4deb44c5@k37g2000hsf.googlegroups.com...
>
>
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

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

<dave.mcmanus@gmail.com> wrote in message
news:8636e1c6-909b-4e62-915a-9631402695a6@m45g2000hsb.googlegroups.com...
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.



On 7 Jul, 18:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> 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.mcma...@gmail.com> wrote in message
>
> news:47fffde8-f7ef-41c0-9b44-7aec4deb44c5@k37g2000hsf.googlegroups.com...
>
>
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

Dave,

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:u3g9NbQ4IHA.4908@TK2MSFTNGP04.phx.gbl...
> 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
>
> <dave.mcmanus@gmail.com> wrote in message
> news:8636e1c6-909b-4e62-915a-9631402695a6@m45g2000hsb.googlegroups.com...
> 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.
>
>
>
> On 7 Jul, 18:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
>> 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.mcma...@gmail.com> wrote in message
>>
>> news:47fffde8-f7ef-41c0-9b44-7aec4deb44c5@k37g2000hsf.googlegroups.com...
>>
>>
>>
>>
>>
>> > 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.- Hide quoted text -
>>
>> - Show quoted text -
>
>