+ Reply to Thread
Results 1 to 1 of 1

Increment columns in INDEX and MATCH formula - ROWS and COLUMNS

  1. #1
    Registered User
    Join Date
    06-19-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    30

    Increment columns in INDEX and MATCH formula - ROWS and COLUMNS

    Hi

    I would be grateful for some help. Attached is a spreadsheet with two tabs, the "data" tab shows the monthly returns of three investment portfolios and the "main" tab is a summary of the performance i.e. returns ytd, 1m, 3m, 6m and 12m. The main tab uses offset for the dynamic range of dates so that the performance automatically updates each month, Cell B1.

    I want to find a quick way of increasing the column references when dragging the formulae down. For example, the three month performance calculation is:

    {=(PRODUCT(INDEX(Data!$B:$B,MATCH($B$1,Data!$A:$A)):INDEX(Data!$B:$B,MATCH($B$1,Data!$A:$A)-2)+1))-1}

    What I want to be able to do is copy the formulae down, which increases the columns from B to D etc... I am stuck, I think it is a ROWS and COLUMNS and is probably quite simple, but need some help figuring it out..!

    Thanks

    Nick
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1