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

1. ## 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!!

2. ## 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}

Like magic!

Tks.

4. ## 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

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

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