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

**ccernat** · 04-04-2012, 04:38 AM

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

**Bob Phillips** · 04-04-2012, 05:25 AM

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}

**ccernat** · 04-04-2012, 06:12 AM

Like magic!

Tks.

**Bob Phillips** · 04-04-2012, 06:24 AM

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