IRR data in different ranges
 

The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they appear to IRR
as one contiguous range?

Not unless you can in a separate area pastelink these non-contiguous ranges
into a single contiguous range.

"GaryF" wrote in message
...
The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they appear to
IRR
as one contiguous range?

What about a function in VB? i.e. Concatenate_Range(First_range,
Second_range) returning an array with all the data (of course I don't know
how to write it, yet).

"N Harkawat" wrote:

Not unless you can in a separate area pastelink these non-contiguous ranges
into a single contiguous range.

"GaryF" wrote in message
...
The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they appear to
IRR
as one contiguous range?

N Harkawat wrote...
Not unless you can in a separate area pastelink these non-contiguous
ranges
into a single contiguous range.

"GaryF" wrote in message
The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they
appear to
IRR as one contiguous range?

It's possible to consolidate multiple ranges into a single array as
long as there's a regular pattern to the different ranges. For example,
the formula

=IRR(N(OFFSET($B$2:$B$11,MOD(ROW($1:$20)-1,10),
2*INT((ROW($1:$20)-1)/10),1,1)),-0.9)

calculates the IRR of the cashflow starting in B2:B11 and continuing in
D2:D11.

i just tested this in Excel 2003: I created a named range containing some
sample values in discontinuous subranges. I then 'aimed' the IRR function at
the named range:
=IRR(IRRValues)
I didn't use a "guess" at first, but then I put guesses ranging from .1 to
1.0 and it didn't make a difference in the answer.
So, IIIFFF your spreadsheet is fairly stable in where the data is going to
be, you can create a named range to include all of the discontinuously laid
out input data. I think the data should be arranged left to right in the
overall layout of the spreadsheet, although it doesn't seem to matter if the
inputs remain parallel. See example below.

This is how I set up the test, the top 31% is the answer using the formula
above which looks at the name containing the discontinuous ranges below the
formulas and to the right. The name IRRValues 'refers to'
=Sheet1!$M$43:$M$47,Sheet1!$O$36:$O$39 in the Insert|Name|Define... dialog
box. I just highlighted all of the cells using the control key before I
called up Insert|Name|Define and typed in a name.
As a check, the bottom 31% looks at the continuous range in between the two
discontinuous ranges that are part of the IRRValues named range. Of course,
the cell lines don't copy...Hope this doesn't discombobulate when I post it...
K L M N O
36 IRR(IRRValues,M38) 31% 300
37 IRR(N43:N51,M38) 31% 122
38 Guess (M38) 0.1 233
39 650
40
41
42
43 -1000 -1000
44 400 400
45 400 400
46 200 200
47 500 500
48 300
49 122
50 233
51 650

Let us know if this helps.

"GaryF" wrote:

The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they appear to IRR
as one contiguous range?

Thanks Guys,
I think I can get SongBear's technique to work on my project (and I'm going
to work through Harlan's formula just for the mental exercise!)

"SongBear" wrote:

i just tested this in Excel 2003: I created a named range containing some
sample values in discontinuous subranges. I then 'aimed' the IRR function at
the named range:
=IRR(IRRValues)
I didn't use a "guess" at first, but then I put guesses ranging from .1 to
1.0 and it didn't make a difference in the answer.
So, IIIFFF your spreadsheet is fairly stable in where the data is going to
be, you can create a named range to include all of the discontinuously laid
out input data. I think the data should be arranged left to right in the
overall layout of the spreadsheet, although it doesn't seem to matter if the
inputs remain parallel. See example below.

This is how I set up the test, the top 31% is the answer using the formula
above which looks at the name containing the discontinuous ranges below the
formulas and to the right. The name IRRValues 'refers to'
=Sheet1!$M$43:$M$47,Sheet1!$O$36:$O$39 in the Insert|Name|Define... dialog
box. I just highlighted all of the cells using the control key before I
called up Insert|Name|Define and typed in a name.
As a check, the bottom 31% looks at the continuous range in between the two
discontinuous ranges that are part of the IRRValues named range. Of course,
the cell lines don't copy...Hope this doesn't discombobulate when I post it...
K L M N O
36 IRR(IRRValues,M38) 31% 300
37 IRR(N43:N51,M38) 31% 122
38 Guess (M38) 0.1 233
39 650
40
41
42
43 -1000 -1000
44 400 400
45 400 400
46 200 200
47 500 500
48 300
49 122
50 233
51 650

Let us know if this helps.

"GaryF" wrote:

The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they appear to IRR
as one contiguous range?

Thanks for the feed back!
Bear

"GaryF" wrote:

Thanks Guys,
I think I can get SongBear's technique to work on my project (and I'm going
to work through Harlan's formula just for the mental exercise!)

"SongBear" wrote:

i just tested this in Excel 2003: I created a named range containing some
sample values in discontinuous subranges. I then 'aimed' the IRR function at
the named range:
=IRR(IRRValues)
I didn't use a "guess" at first, but then I put guesses ranging from .1 to
1.0 and it didn't make a difference in the answer.
So, IIIFFF your spreadsheet is fairly stable in where the data is going to
be, you can create a named range to include all of the discontinuously laid
out input data. I think the data should be arranged left to right in the
overall layout of the spreadsheet, although it doesn't seem to matter if the
inputs remain parallel. See example below.

This is how I set up the test, the top 31% is the answer using the formula
above which looks at the name containing the discontinuous ranges below the
formulas and to the right. The name IRRValues 'refers to'
=Sheet1!$M$43:$M$47,Sheet1!$O$36:$O$39 in the Insert|Name|Define... dialog
box. I just highlighted all of the cells using the control key before I
called up Insert|Name|Define and typed in a name.
As a check, the bottom 31% looks at the continuous range in between the two
discontinuous ranges that are part of the IRRValues named range. Of course,
the cell lines don't copy...Hope this doesn't discombobulate when I post it...
K L M N O
36 IRR(IRRValues,M38) 31% 300
37 IRR(N43:N51,M38) 31% 122
38 Guess (M38) 0.1 233
39 650
40
41
42
43 -1000 -1000
44 400 400
45 400 400
46 200 200
47 500 500
48 300
49 122
50 233
51 650

Let us know if this helps.

"GaryF" wrote:

The data I wish to perform IRR calcs on is in different ranges in the
worksheet. Is there any way to concatenate these ranges so they appear to IRR
as one contiguous range?