| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with arrays as arguments
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Working with arrays as arguments
If C20 is in the same sheet 'Cash Flows', try with a named range.
--From menu InsertNameDefine 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 --------------- Jacob Skaria "HB" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Working with arrays as arguments
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... "Jacob Skaria" wrote: If C20 is in the same sheet 'Cash Flows', try with a named range. --From menu InsertNameDefine 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 --------------- Jacob Skaria "HB" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Working with arrays as arguments
Maybe this entered using CTRL+SHIFT+ENTER:
=IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:A E9)),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) "HB" wrote: 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... "Jacob Skaria" wrote: If C20 is in the same sheet 'Cash Flows', try with a named range. --From menu InsertNameDefine 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 --------------- Jacob Skaria "HB" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Working with arrays as arguments
Lori, does that need to be array entered?
"Lori Miller" wrote: Maybe this entered using CTRL+SHIFT+ENTER: =IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:A E9)),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) "HB" wrote: 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... "Jacob Skaria" wrote: If C20 is in the same sheet 'Cash Flows', try with a named range. --From menu InsertNameDefine 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 --------------- Jacob Skaria "HB" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Working with arrays as arguments
I think it needs array-entry, but it should have been a horizontal array:
=IRR(IF(TRANSPOSE(FREQUENCY(COLUMN(CF!E9:AE9),COLU MN(CF!E9:AE9))), CF!E9:AE9,C20),0.1) "Jacob Skaria" wrote: Lori, does that need to be array entered? "Lori Miller" wrote: Maybe this entered using CTRL+SHIFT+ENTER: =IRR(IF(FREQUENCY(COLUMN(CF!E9:AE9),COLUMN(CF!E9:A E9)),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) "HB" wrote: 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... "Jacob Skaria" wrote: If C20 is in the same sheet 'Cash Flows', try with a named range. --From menu InsertNameDefine 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 --------------- Jacob Skaria "HB" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Working with arrays as arguments
Hello HB,
Enter =IRR(TRANSPOSE('Cash Flows'!E9:AE9),0.1) normally (no array-formula). IRR expects a vertical array input. Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Too few arguments | Excel Worksheet Functions | |||
| Working with 2 arguments over 4 columns (countif ??) | Excel Worksheet Functions | |||
| And, if, arguments.... | Excel Worksheet Functions | |||
| SUMPRODUCT with 3 arrays not working | Excel Worksheet Functions | |||
| Working with Arrays | Excel Worksheet Functions | |||
Hybrid Mode
