Working with arrays as arguments

  • Thread starter Thread starter HB
  • Start date Start date
H

HB

Hello

I am trying to calculate the IRR of a series of cashflows stored
sequentially, except that I need to append a final term to the cash flows
within the formula itself.
i.e. In my formula :
=IRR('Cash Flows'!E9:AE9,0.1)
I need to modify the first argument and add a new term from another cell in
my excel sheet. The first argument in the formula above is an array of
numbers, so basically what I am trying to do is dynamically add a new term at
the end of the array.

I tried variations like:
=IRR({'Cash Flows'!E9:AE9}&{C20},0.1) ; where C20 represents the term I want
to append but none of them seem to work.

It is not an option for me to store the number in another excel sheet (i.e.
I cant enter the number in AF9 and then process the formula with E9:AF9)

Would appreciate it if anyone can help me out with this.

Thanks
HB
 
If C20 is in the same sheet 'Cash Flows', try with a named range.

--From menu Insert>Name>Define
Names in workbook: myRange
Refers to: ='Cash Flows'!$E$9:$AE$9,'Cash Flows'!$C$20

Hit OK

--Try the formula
=IRR(myRange,0.1)


If this post helps click Yes
 
Thanks Jacob, but unfortunately the cells are in different sheets...

Ill check if I can transfer some of the cells into the same sheet, but in
the meanwhile if anyone has an idea how to combine ranges in different sheet
it would be really helpful...
 
Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:AE9)),CF!E9:AE9,C20),0.1)

where "CF" is the cash flow sheet.
If both ranges are on the same sheet also try:=IRR((E9:AE9,C20),0.1)
 
Lori, does that need to be array entered?

Lori Miller said:
Maybe this entered using CTRL+SHIFT+ENTER:

=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:AE9)),CF!E9:AE9,C20),0.1)

where "CF" is the cash flow sheet.
If both ranges are on the same sheet also try:=IRR((E9:AE9,C20),0.1)
Click to expand...
 
I think it needs array-entry, but it should have been a horizontal array:

=IRR(IF(TRANSPOSE(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:AE9))),
CF!E9:AE9,C20),0.1)
 
Hello HB,

Enter
=IRR(TRANSPOSE('Cash Flows'!E9:AE9),0.1)
normally (no array-formula). IRR expects a vertical array input.

Regards,
Bernd
 
Back
Top