Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Excel array functions - select, add & return result [email protected] Excel Worksheet Functions 0 July 12th 07 06:36 PM
Query on how I would calculate a result in Excel? confused Excel Worksheet Functions 3 December 6th 06 11:10 AM
select cells that are the result of a search john mcmichael Excel Discussion (Misc queries) 3 November 8th 06 10:26 PM
Can I create a fill-in form on excel and then calculate the result Frusterated Excel Discussion (Misc queries) 0 July 6th 06 09:17 PM
select numbers from a list and add to optimise result DT Excel Worksheet Functions 1 January 17th 06 11:27 AM


All times are GMT +1. The time now is 05:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"