Naming a range inside a formula using "Address"

[mcke1610]

Hi...I'm trying to use the address formula inside of an IRR function. Is this possible? Here is exactly what is happening:

-There is a table with a list of dates going back a few years and related cash flows
-I want to be able to calculate the IRR for specific date ranges (ie: i would input from 1-jan-06 to 31-dec-06.
-I first use MATCH to find the row that corresponds to the start and end dates
-I then use ADDRESS to get those rows into proper notation
-I then use IRR
=IRR("L" & VALUE(H590) & ":" & "L" & VALUE(H594),H596 & ":" & H597,"")

In this case H590 and H594 hold the row numbers while H596 and H597 hold addresses (eg: $A$456)

Can I do this?
THanks!

[starguy]

Hi...I'm trying to use the address formula inside of an IRR function. Is this possible? Here is exactly what is happening:

-There is a table with a list of dates going back a few years and related cash flows
-I want to be able to calculate the IRR for specific date ranges (ie: i would input from 1-jan-06 to 31-dec-06.
-I first use MATCH to find the row that corresponds to the start and end dates
-I then use ADDRESS to get those rows into proper notation
-I then use IRR
=IRR("L" & VALUE(H590) & ":" & "L" & VALUE(H594),H596 & ":" & H597,"")

In this case H590 and H594 hold the row numbers while H596 and H597 hold addresses (eg: $A$456)

Can I do this?
THanks!

you can use INDIRECT function to get the results.
see help on using this function

[mcke1610]

Can you provide an example of how indirect would work?

I'm just not clear on how the INDIRECT function will help me get this range into the IRR function.

Regards.

[daddylonglegs]

Rather than using ADDRESS, MATCH and INDIRECT a better way, normally, is to use a combination of INDEX and MATCH, INDEX can return a cell reference which can be used within another formula. You can probably even do all that within one formula.

What are the MATCH and ADDRESS formulas you are using?

[starguy]

Can you provide an example of how indirect would work?

I'm just not clear on how the INDIRECT function will help me get this range into the IRR function.

Regards.

put here a sample file with formula you use.

[mcke1610]

I've attached an example file.

Basically there are cash flows over the past 5 years. What I want to be able to calculate is the IRR over specified periods. So in the blue highlighted cells I input start and end dates.

I can determine where the start/end dates are in the range...it's taking that data and getting it to work in the IRR function that is proving difficult.

Thanks for all of your help.

[daddylonglegs]

It's a little long but this formula in F580 should do what you want....

=XIRR(INDEX(G8:G572,MATCH(F579-1,A8:A572)+1):INDEX(G8:G572,MATCH(F580,A8:A572)), INDEX(A8:A572,MATCH(F579-1,A8:A572)+1): INDEX(A8:A572,MATCH(F580,A8:A572)),0.03)

format as %