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
)
)