+ Reply to Thread
Results 1 to 2 of 2

Formula to refer to array in another sheet that shifts based on date

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    New Zealand
    MS-Off Ver
    Excel for Mac 2011
    Posts
    1

    Question Formula to refer to array in another sheet that shifts based on date

    Hi guys. I'm currently trying to build a formula that takes a list of quarterly electricity prices, quarterly dates, a quarterly electricity consumption figure, and a consumer start date to calculate an overall power cost for different consumers. The formula will be placed in sheet one with the consumption figure and start date, while the prices and quarterly dates are in sheet two.

    The formula should multiply the consumption by each quarterly unit price in the array that have an associated date occurring after the start date (in this case I2 onwards), and summed. In this case this adds up to $4610. Of course if the start date was for instance, 1/1/13, then the array would consist of I8 onwards. I thought I could do this with an index/match but got stuck trying to work out the logic for changing the range based on the start date. If anyone has any input that would be greatly appreciated. Example sheet is attached, hopefully I've explained myself clearly, let me know if not. Cheers in advanceExample.xlsx.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Formula to refer to array in another sheet that shifts based on date

    Hi Vertex34

    Welcome to the forum.


    I have a solution (of sorts)

    I started off using helper rows 2:5 so that I can see what is going on.

    Because of this, the formula will not copy down from D8 - so it will only work for one row.

    So then, on sheet 3, I combined the formulas from rows 2,3 and 4 and incorporated them into 1 formula in row 8. It works - but I would not like to have to make changes to this formula ! However, it does copy down, so I suppose that to that extent, it does work.

    Let me know what you think

    Regards
    Alastair
    Attached Files Attached Files
    Last edited by aydeegee; 10-03-2014 at 10:08 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using Array to refer Sheet Names
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2013, 08:15 PM
  2. Replies: 7
    Last Post: 12-16-2012, 04:24 PM
  3. Need a formula to copy shifts from sheet 1 to sheet 2
    By lsafri in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-23-2012, 03:35 PM
  4. Refer to a named range in a CSE array formula?
    By JayUSA in forum Excel General
    Replies: 3
    Last Post: 01-11-2010, 10:56 PM
  5. copy date based on date -refer to date range
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2006, 08:35 AM

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