+ Reply to Thread
Results 1 to 4 of 4

Average - 3 months / 6 months trend line ( array formula? )

  1. #1
    Registered User
    Join Date
    07-19-2011
    Location
    Bucharest
    MS-Off Ver
    Excel 2003
    Posts
    24

    Average - 3 months / 6 months trend line ( array formula? )

    Hello guys,

    In the attachment you will find 2 sheets: TRENDLINE - TABLA and VALUE ENTRIES. In the first sheet, TRENDLINE, i have as rows a list of item codes, and as columns some headers for Avg. unit price / 3months and 6months.

    For each item code in the TRENDLINE, there are n numbers of transaction by date in the Value Entries sheet, and each transaction has a Cost per Unit column with data. Now, based on the Date of today - 3 months or 6 months, i want to calculate the average for each item in the TRENDLINE LIST, and also if possible, to have some visual signs with UP, DOWN, or - depending on the trend of the Cost per unit.

    I tried all kind of tricks, but mostly manual, i need something automatic, because the tables are querying a database for the data.

    Tks ALOT!!
    Attached Files Attached Files
    Last edited by ccernat; 04-04-2012 at 06:13 AM.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Average - 3 months / 6 months trend line ( array formula? )

    This will get the average last 3 months

    =IFERROR(SUMPRODUCT(--('VALUE ENTRIES'!$A$1:$A$10000=$B8),--('VALUE ENTRIES'!$B$1:$B$10000>=MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-{3,2},DAY(TODAY())*{1,0}))),'VALUE ENTRIES'!$O$1:$O$10000)
    /SUMPRODUCT(--('VALUE ENTRIES'!$A$1:$A$10000=$B8),--('VALUE ENTRIES'!$B$1:$B$10000>=MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-{3,2},DAY(TODAY())*{1,0})))),"")

    For last 6 months, change {3,2} to {6,5}

  3. #3
    Registered User
    Join Date
    07-19-2011
    Location
    Bucharest
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Average - 3 months / 6 months trend line ( array formula? )

    Like magic!

    Tks.

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Average - 3 months / 6 months trend line ( array formula? )

    You can simplify this if you use an intermediate cell.

    So, say you put the calcualtion of the earliest dates in D3 and E3

    =MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-{3,2},DAY(TODAY())*{1,0}))
    =MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-{6,5},DAY(TODAY())*{1,0}))

    you could then simply use

    =IFERROR(AVERAGEIFS('VALUE ENTRIES'!$O$1:$O$10000,'VALUE ENTRIES'!$A$1:$A$10000,$B8,'VALUE ENTRIES'!$B$1:$B$10000,">="&D$3),"")

    copied down and across

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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