How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to help
explain my question.


--
Wag more, bark less

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge, there
is "no way" to specify non-contiguous arrays with XIRR, other than writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to help
explain my question.


--
Wag more, bark less

No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge, there
is "no way" to specify non-contiguous arrays with XIRR, other than writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to help
explain my question.


--
Wag more, bark less

A UDF would copy the cells to an unused portion (or unused sheet), then do
the calculation. Why not just do this yourself in your spreadsheet?

Regards,
Fred

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That is
not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

"Brad" wrote:
The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Well, obviously. My bad! I was rushed and misread your syntax, which was
clear on second thought. Sorry about that.


I'm willing to have my UDF, but not sure how that
would be done.

I or someone can help you with that -- eventually. In the meantime, you
might do a Google Groups search. I believe a UDF was posted not too long ago.


----- original message -----

"Brad" wrote:
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge, there
is "no way" to specify non-contiguous arrays with XIRR, other than writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to help
explain my question.


--
Wag more, bark less

"Fred Smith" wrote:
A UDF would copy the cells to an unused portion (or unused sheet), then
do the calculation. Why not just do this yourself in your spreadsheet?

The manual approach is reasonable to do if it must be done just a few times.
But it is a challenge to make that work for any arbitrary set of value and
date ranges.

As you say, the simple UDF would copy and execute Evaluate("XIRR(...,...)").
But I believe someone showed how to call the XIRR add-in (in Excel 2003)
directly in VBA. The key is using a Reference, which I do not remember
off-hand.

Oh, I see I have it already (atpvbaen.xla). Well, the key is remembering
how to set that up and the syntax for the call. All that escapes me at the
moment. As icing on the cake, it would be nice if the UDF worked with a
reference union directly; alternatively, we can define an ad hoc calling
sequence for specifying both the value list and date list to be
variable-length.


----- original message -----

"Fred Smith" wrote in message
...
A UDF would copy the cells to an unused portion (or unused sheet), then do
the calculation. Why not just do this yourself in your spreadsheet?

Regards,
Fred

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That
is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

I wrote:
But I believe someone showed how to call the XIRR add-in
(in Excel 2003) directly in VBA.

I meant to add: but in Excel 2007, we might be able to write simply
WorksheetFunction.Xirr(...,...).

Also, I believe I stumbled upon differences between the the atpvbaen.xla
implementation of XIRR and the ATP XIRR that Excel 2003 uses. I would not
be surprised if there were also differences between the VBA and Excel
implementations in Excel 2007. As I recall (vaguely and perhaps
incorrectly), the differences were in error handling, which the ATP XIRR
does not do well anyway. So the differences might be of little or no
concern.

For some reason, I am having difficulty finding the discussion of all this
not too long ago. That's why I'm being vague. And I hope my comments are
not a misdirection. (Sorry*2.)


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Fred Smith" wrote:
A UDF would copy the cells to an unused portion (or unused sheet), then
do the calculation. Why not just do this yourself in your spreadsheet?

The manual approach is reasonable to do if it must be done just a few
times. But it is a challenge to make that work for any arbitrary set of
value and date ranges.

As you say, the simple UDF would copy and execute
Evaluate("XIRR(...,...)"). But I believe someone showed how to call the
XIRR add-in (in Excel 2003) directly in VBA. The key is using a
Reference, which I do not remember off-hand.

Oh, I see I have it already (atpvbaen.xla). Well, the key is remembering
how to set that up and the syntax for the call. All that escapes me at
the moment. As icing on the cake, it would be nice if the UDF worked with
a reference union directly; alternatively, we can define an ad hoc calling
sequence for specifying both the value list and date list to be
variable-length.


----- original message -----

"Fred Smith" wrote in message
...
A UDF would copy the cells to an unused portion (or unused sheet), then do
the calculation. Why not just do this yourself in your spreadsheet?

Regards,
Fred

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That
is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

"Brad" wrote:
I'm willing to have my UDF, but not sure how that would be done.

The following UDF seems to have the same results for comparable Excel XIRR
usage.

For Excel 2003, you must select the add-in "Analysis ToolPak - VBA" in Excel
(Tools Add-ins), and you must select the reference "atpvbaen.xls" in VBA
(Tools References).

The UDF should work with your examples, when written correctly:

=myXIRR((m3:m4,n5), d3:d5)
=myXIRR((m3:m40,n41), d3:d41)

However, for thorough testing of any solution, I suggest that you set B1:B7
to the values -10000, 2000, 3000, 4000, 5000, 6000, 7000, and set C1:C7 to
the dates 1/1/2011 through 1/1/2017. Then....

1. Compare to the following, all of which should return the same valid
result (about 29.79%):

=XIRR(B1:B7, C1:C7)
=myXIRR(B1:B7, C1:C7)
=myXIRR((B1,B2:B6,B7), (C1:C2,C3,C4,C5:C7))
=myXIRR({-10000,2000,3000,4000,5000,6000,7000}, C1:C7)

2. Compare the following error conditions:

a. =XIRR({-1E9,1}, C1:C2)
=myXIRR({-1E9,1}, C1:C2)

b. =XIRR({-10000}, C1)
=myXIRR({-10000}, C1)

c. =XIRR(-10000, C1)
=myXIRR(-10000, C1)

d. =XIRR("hi", C1)
=myXIRR("hi", C1)


To enter the UDF, copy the following text. In Excel, press alt+F11. In
VBA, click Insert Module, then paste into the VBA editor pane. Also see
the special steps for Excel 2003 above.

The UDF....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range": nv = v.Count
Case "Variant()": nv = UBound(v)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(d)
Case "Range": nd = d.Count
Case "Variant()": nd = UBound(d)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
ReDim vv(1 To nv)
ReDim dd(1 To nd)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That is
not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

I see that Ron posted a solution at about the same time that I did.
Combining the best of both....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range":
ReDim vv(1 To v.Count)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
Case "Variant()": vv = v
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(v)
Case "Range":
ReDim dd(1 To d.Count)
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
Case "Variant()": dd = d
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Brad" wrote:
I'm willing to have my UDF, but not sure how that would be done.

The following UDF seems to have the same results for comparable Excel XIRR
usage.

For Excel 2003, you must select the add-in "Analysis ToolPak - VBA" in
Excel (Tools Add-ins), and you must select the reference "atpvbaen.xls"
in VBA (Tools References).

The UDF should work with your examples, when written correctly:

=myXIRR((m3:m4,n5), d3:d5)
=myXIRR((m3:m40,n41), d3:d41)

However, for thorough testing of any solution, I suggest that you set
B1:B7 to the values -10000, 2000, 3000, 4000, 5000, 6000, 7000, and set
C1:C7 to the dates 1/1/2011 through 1/1/2017. Then....

1. Compare to the following, all of which should return the same valid
result (about 29.79%):

=XIRR(B1:B7, C1:C7)
=myXIRR(B1:B7, C1:C7)
=myXIRR((B1,B2:B6,B7), (C1:C2,C3,C4,C5:C7))
=myXIRR({-10000,2000,3000,4000,5000,6000,7000}, C1:C7)

2. Compare the following error conditions:

a. =XIRR({-1E9,1}, C1:C2)
=myXIRR({-1E9,1}, C1:C2)

b. =XIRR({-10000}, C1)
=myXIRR({-10000}, C1)

c. =XIRR(-10000, C1)
=myXIRR(-10000, C1)

d. =XIRR("hi", C1)
=myXIRR("hi", C1)


To enter the UDF, copy the following text. In Excel, press alt+F11. In
VBA, click Insert Module, then paste into the VBA editor pane. Also see
the special steps for Excel 2003 above.

The UDF....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range": nv = v.Count
Case "Variant()": nv = UBound(v)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(d)
Case "Range": nd = d.Count
Case "Variant()": nd = UBound(d)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
ReDim vv(1 To nv)
ReDim dd(1 To nd)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That
is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

"Joe User" <joeu2004 wrote in message:
I see that Ron posted a solution at about the same time that I did.
Combining the best of both....

On second thought, Ron's version has all the same error results that mine
and Excel XIRR have. So it may be the better implementation, since it is
tighter. See my follow-up comments to Ron's posting.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
I see that Ron posted a solution at about the same time that I did.
Combining the best of both....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range":
ReDim vv(1 To v.Count)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
Case "Variant()": vv = v
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(v)
Case "Range":
ReDim dd(1 To d.Count)
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
Case "Variant()": dd = d
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Brad" wrote:
I'm willing to have my UDF, but not sure how that would be done.

The following UDF seems to have the same results for comparable Excel
XIRR usage.

For Excel 2003, you must select the add-in "Analysis ToolPak - VBA" in
Excel (Tools Add-ins), and you must select the reference "atpvbaen.xls"
in VBA (Tools References).

The UDF should work with your examples, when written correctly:

=myXIRR((m3:m4,n5), d3:d5)
=myXIRR((m3:m40,n41), d3:d41)

However, for thorough testing of any solution, I suggest that you set
B1:B7 to the values -10000, 2000, 3000, 4000, 5000, 6000, 7000, and set
C1:C7 to the dates 1/1/2011 through 1/1/2017. Then....

1. Compare to the following, all of which should return the same valid
result (about 29.79%):

=XIRR(B1:B7, C1:C7)
=myXIRR(B1:B7, C1:C7)
=myXIRR((B1,B2:B6,B7), (C1:C2,C3,C4,C5:C7))
=myXIRR({-10000,2000,3000,4000,5000,6000,7000}, C1:C7)

2. Compare the following error conditions:

a. =XIRR({-1E9,1}, C1:C2)
=myXIRR({-1E9,1}, C1:C2)

b. =XIRR({-10000}, C1)
=myXIRR({-10000}, C1)

c. =XIRR(-10000, C1)
=myXIRR(-10000, C1)

d. =XIRR("hi", C1)
=myXIRR("hi", C1)


To enter the UDF, copy the following text. In Excel, press alt+F11. In
VBA, click Insert Module, then paste into the VBA editor pane. Also
see the special steps for Excel 2003 above.

The UDF....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range": nv = v.Count
Case "Variant()": nv = UBound(v)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(d)
Case "Range": nd = d.Count
Case "Variant()": nd = UBound(d)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
ReDim vv(1 To nv)
ReDim dd(1 To nd)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That
is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

For posterity....

I wrote:
Select Case TypeName(v)
Case "Range":
ReDim dd(1 To d.Count)

That second Select statement should be:

Select Case TypeName(d)

It was right in my original version.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
I see that Ron posted a solution at about the same time that I did.
Combining the best of both....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range":
ReDim vv(1 To v.Count)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
Case "Variant()": vv = v
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(v)
Case "Range":
ReDim dd(1 To d.Count)
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
Case "Variant()": dd = d
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Brad" wrote:
I'm willing to have my UDF, but not sure how that would be done.

The following UDF seems to have the same results for comparable Excel
XIRR usage.

For Excel 2003, you must select the add-in "Analysis ToolPak - VBA" in
Excel (Tools Add-ins), and you must select the reference "atpvbaen.xls"
in VBA (Tools References).

The UDF should work with your examples, when written correctly:

=myXIRR((m3:m4,n5), d3:d5)
=myXIRR((m3:m40,n41), d3:d41)

However, for thorough testing of any solution, I suggest that you set
B1:B7 to the values -10000, 2000, 3000, 4000, 5000, 6000, 7000, and set
C1:C7 to the dates 1/1/2011 through 1/1/2017. Then....

1. Compare to the following, all of which should return the same valid
result (about 29.79%):

=XIRR(B1:B7, C1:C7)
=myXIRR(B1:B7, C1:C7)
=myXIRR((B1,B2:B6,B7), (C1:C2,C3,C4,C5:C7))
=myXIRR({-10000,2000,3000,4000,5000,6000,7000}, C1:C7)

2. Compare the following error conditions:

a. =XIRR({-1E9,1}, C1:C2)
=myXIRR({-1E9,1}, C1:C2)

b. =XIRR({-10000}, C1)
=myXIRR({-10000}, C1)

c. =XIRR(-10000, C1)
=myXIRR(-10000, C1)

d. =XIRR("hi", C1)
=myXIRR("hi", C1)


To enter the UDF, copy the following text. In Excel, press alt+F11. In
VBA, click Insert Module, then paste into the VBA editor pane. Also
see the special steps for Excel 2003 above.

The UDF....


Option Explicit

Function myXIRR(v, d, Optional g As Double = 0.1)
Dim vv, dd, c, i As Long, nv As Long, nd As Long
Select Case TypeName(v)
Case "Range": nv = v.Count
Case "Variant()": nv = UBound(v)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
Select Case TypeName(d)
Case "Range": nd = d.Count
Case "Variant()": nd = UBound(d)
Case "Double": GoTo naError
Case Else: GoTo valerror
End Select
ReDim vv(1 To nv)
ReDim dd(1 To nd)
i = 0: For Each c In v: i = i + 1: vv(i) = c: Next
i = 0: For Each c In d: i = i + 1: dd(i) = c: Next
myXIRR = xirr(vv, dd, g)
Exit Function

naError: myXIRR = CVErr(xlErrNA): Exit Function
valerror: myXIRR = CVErr(xlErrValue)
End Function


----- original message -----

"Brad" wrote in message
...
No, I mean XIRR

The stream of "m" and "n"s are the values
the strream of "d" are the dates.

Not sure what you mean that the number of cells are not the same
M3:M4 and N5 are three values
D3:D5 are three dates

M3:M40 and N41 are 39 values
D3:D41 are 39 dates

I'm willing to have my UDF, but not sure how that would be done.


"Joe User" wrote:

"Brad" wrote:
I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

Your examples do not make sense. Do you mean IRR instead of XIRR?

XIRR takes two arrays: XIRR(values,dates). (There is also an optional
"guess" parameter.) In your examples, if M represents values and N
represents dates, the number of cells must be the same for both. That
is not
the case in your examples.

For IRR, you can use the union operator. For example,
IRR((M3:M40,N41,D3:D41)). Note that the "extra" set of parentheses is
required.

However, XIRR does not support the union operator. To my knowledge,
there
is "no way" to specify non-contiguous arrays with XIRR, other than
writing
your own UDF.


----- original message -----

"Brad" wrote:
How can I "trick" excel to use non-continuous cell

I would like to have
=xirr(m3:m4 and n5,d3:d5)
or
=xirr(m3:m40 and n41,d3:d41)

I realize that xirr doesn't accept the "and" . I'm including it to
help
explain my question.


--
Wag more, bark less

On Wed, 14 Apr 2010 21:30:23 -0700, "Joe User" <joeu2004 wrote:

I was right the first time: Harlan's verion works just fine with explicit
reference unions, too.

It's been years since I used his unmodified version, and I've never checked
that out. And I don't recall what version of Excel I was using when he first
provided that. But that's good to know.
--ron

"Ron Rosenfeld" wrote:
On Wed, 14 Apr 2010 21:30:23 -0700, "Joe User" <joeu2004 wrote:
[....] Harlan's verion works just fine with explicit
reference unions, too.

[....] I don't recall what version of Excel I was using
when he first provided that. But that's good to know.

Right. Harlan posted that implementation at least as long ago as Dec 2003.
Harlan did not refer to any earlier posting with that implementation. No
mention of the Excel or VBA version in the thread. See
groups.google.com/group/microsoft.public.excel.misc/msg/bb003cebc1f121c5.


----- original message -----

"Ron Rosenfeld" wrote in message
...
On Wed, 14 Apr 2010 21:30:23 -0700, "Joe User" <joeu2004 wrote:

I was right the first time: Harlan's verion works just fine with explicit
reference unions, too.

It's been years since I used his unmodified version, and I've never
checked
that out. And I don't recall what version of Excel I was using when he
first
provided that. But that's good to know.
--ron

On Thu, 15 Apr 2010 08:25:52 -0700, "Joe User" <joeu2004 wrote:

However, note that v must actually contain a value. It is not sufficient to
merely declare the object type of v. For instance:

I agree that it is NOT sufficient to merely declare the object type. However,
I don't believe it is strictly true that the object must actually contain a
value. There are objects that do not even support the Value property.



--ron

"Ron Rosenfeld" wrote:
I agree that it is NOT sufficient to merely declare the object type.
However, I don't believe it is strictly true that the object must
actually contain a value. There are objects that do not even support
the Value property.

Well, we can quibble over what I meant by the word "value". It certainly is
not limited to mean that there is a Value property per se in the object.


----- original message -----

"Ron Rosenfeld" wrote in message
...
On Thu, 15 Apr 2010 08:25:52 -0700, "Joe User" <joeu2004 wrote:

However, note that v must actually contain a value. It is not sufficient
to
merely declare the object type of v. For instance:

I agree that it is NOT sufficient to merely declare the object type.
However,
I don't believe it is strictly true that the object must actually contain
a
value. There are objects that do not even support the Value property.



--ron

On Thu, 15 Apr 2010 14:55:40 -0700, "Joe User" <joeu2004 wrote:

Well, we can quibble over what I meant by the word "value". It certainly is
not limited to mean that there is a Value property per se in the object.

OK, I was using in that sense, since that is how I determine the Value of a VB
object. When you wrote that, my first attempt at verifying was to look for the
Value of a regular expression object. (e.g. re.value)

If you want to use it to mean something else, that's OK with me.

--ron

"Ron Rosenfeld" wrote:
If you want to use it to mean something else, that's OK with me.

Thank you. I hope the next time that I mention the "value" of a Double
variable v, you do not chide me because v.Value does not work :-) :-).


----- original message -----

"Ron Rosenfeld" wrote in message
...
On Thu, 15 Apr 2010 14:55:40 -0700, "Joe User" <joeu2004 wrote:

Well, we can quibble over what I meant by the word "value". It certainly
is
not limited to mean that there is a Value property per se in the object.

OK, I was using in that sense, since that is how I determine the Value of
a VB
object. When you wrote that, my first attempt at verifying was to look for
the
Value of a regular expression object. (e.g. re.value)

If you want to use it to mean something else, that's OK with me.

--ron

On Thu, 15 Apr 2010 19:05:54 -0700, "Joe User" <joeu2004 wrote:

Thank you. I hope the next time that I mention the "value" of a Double
variable v, you do not chide me because v.Value does not work :-) :-).

Of course not. But (more definitions now :-))) I don't think I would consider
a Double to be an Object, in the sense we've been discussing.

As you wrote:
"Note TypeOf cannot be used with hard data types such as
Long, Integer, and so forth other than Object."

--ron