Passing multiple references as one argument

[Rick B]

How can I pass multiple references as one argument in a function. For example using the IRR
function...IRR(values,guess):

If I have values in both a contiguous range (say C2:C4) and in one other cell (E12)...how do I
provide the function with it's required values?

If I use this
IRR(E12,C2:C4), then C2:C4 becomes an interest rate?

and this
IRR(E12,C2:C4,8%)...then there are to many arguments.

Thanks,

Rick B

 

[Harlan Grove]

How can I pass multiple references as one argument in a function. For example
using the IRR function...IRR(values,guess):

...

Most functions have a set number of arguments. In its infinite wisdom, Microsoft
got cute with IRR, failing to follow 123's @IRR function's argument order but
adopting its stupid restriction on requiring a single range as the cashflows
argument. As a practical matter, this means it's impossible to overcome this
limitation.

Anyway, Excel's IRR accepts only sinlge area ranges and arrays. To use the
values in multiple, nonadjacent ranges, it's necessary to collect the values in
an array. Given your example, you could use the array formula

=IRR(IF(ROW(1:4)=1,E12,OFFSET(C2:C4,-1,0,4,1)))

More generally, you may have to use something like

=IRR(N(OFFSET(C2:E12,{10;0;1;2},{2;0;0;0},1,1)))