Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with excel array - select, add and calculate result
Hi, I've got the following data for which I'm trying to calculate the
XIRR() result for data up till 31 Mar 07. Row \ Col -- A B C D E F 1 Date Amt Adjustment 31/3/2007 100 2 01-01-2006 -100 3 01-06-2006 40 4 01-01-2007 5 5 01-06-2007 10 Notes 1. The arrays / ranges that satisfies the 31 Mar 07 criteria are A2:B4 and E1:F1. 2. The cells with the adjustment (E1:F1) are in another part of the worksheet (ie not in Col A & B). My approach attempts to add the 2 arrays (A2:B4 & E1:F1) of different sizes. The correct ans is XIRR = 68.4%, which I'm still trying to get, but have problem with getting the offset function right (below). Appreciate all help!! TIA! =XIRR( ( ($A$2:$A$5)*($A$2:$A$5<=DATE(2007,3,31))+ OFFSET($E$1,0,0,ROWS($A$2:$A$5),COLUMNS($A$2:$A$5) )), **Adding E1 ( ($B$2:$B$5)*($A$2:$A$5<=DATE(2007,3,31))+ OFFSET($F$1,0,0,ROWS($A$2:$A$5),COLUMNS($A$2:$A$5) ) ** Adding F1 ) ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Excel array functions - select, add & return result | Excel Worksheet Functions | |||
Query on how I would calculate a result in Excel? | Excel Worksheet Functions | |||
select cells that are the result of a search | Excel Discussion (Misc queries) | |||
Can I create a fill-in form on excel and then calculate the result | Excel Discussion (Misc queries) | |||
select numbers from a list and add to optimise result | Excel Worksheet Functions |