Weighted Average

[ralexander2209]

I am running a price analysis on products that we have purchased last year and attempting to compare it to the market average each week.

To identify the products we have assigned 4 letter product codes to each product (LAMD, LANR ect.).

The information I want the weighted average for is in another workbook which contains our market research on the market average each week. This workbook is linked to the current workbook I am working in.

What I want to do is average the prices of a specific product in the "interactive graph". The problem I'm running into is getting the range and the "tags" to index the correct data. I'm getting a #Value error.

What I have so far:

=AVERAGEIFS(PRICES1,'[Interactive Lumber Graph.xlsm]Lumber1 History'!$B$4:$B$1000,>=StartDate,'[Interactive Lumber Graph.xlsm]Lumber1 History'!$B$4:$B$1000,"<=EndDate",_TAG1,AD2)

PRICES1: the price range of ALL of the products that our company sells
I am then linking the dates of all the dates that we have in the graph...I have a name range but for some reason it won't let me select it...the name is "DATES1"

>=STARTDATE= a link to the start date of the analysis and this is using the "DATES1" Range again

<=ENDDATE= self explanatory (please ask if you are lost)

Then I am trying to link the "TAGS" to the specific product tag
_TAG1: ALL OF THE PRODUCT TAGS IN OUR COMPANY.
AD2 the tag I am looking for the weighted average.


Been working on this for a couple days by myself and finally conceded to the help of strangers haha

 

[ndsutherland]

For starters, I believe your criteria 1 and 2 should look like

">="&StartDate

and

"<="&EndDate

 

[ralexander2209]

For starters, I believe your criteria 1 and 2 should look like

">="&StartDate

and

"<="&EndDate

With this change, I still comes up with #value error.

 

[ralexander2209]

This is the formula I have now.

=AVERAGEIFS(PRICES1,'[Interactive Lumber Graph.xlsm]Lumber1 History'!$B$4:$B$1000,">="&StartDate,'[Interactive Lumber Graph.xlsm]Lumber1 History'!$B$4:$B$1000,"<="&EndDate,_TAG1,AD2)

 

[ndsutherland]

Are your average range and all 3 criteria ranges the same size? (same number of rows and columns)

 

[ralexander2209]

Are your average range and all 3 criteria ranges the same size? (same number of rows and columns)

Yes, it should be just the range of numbers from the specific product "tag" and the date range that I've specified.

I want to be able to change the date range at any time. Allowing me to pull the average price attached to the specific product according to the date range I specify from our market research.

 

[ndsutherland]

A sample would be great, but could you at least post what your named ranges represent? (workbook name, sheet name, and range)

 

[ndsutherland]

I am picturing something like this:
http://1drv.ms/1BP70nw

Unfortunately, named ranges are limited to their own workbook.

 

[ralexander2209]

Okay, I can't give most of the information, as it is confidential. As you can see below, the products each have their own product code and prices throughout a time period. Dating back from 2002 until today. I want to be able to pull the specific product tags from the TABLE B and find that product code in TABLE A according to a specific date range.

TABLE A.
[table="width: 500, class: grid, align: center"]
[tr]
[td][/td]
[td]LCBP[/td]
[td]LCBR[/td]
[/tr]
[tr]
[td]Date[/td]
[td]2x4 SYP[/td]
[td]2x6 SYP[/td]
[/tr]
[tr]
[td]1/4/2015[/td]
[td]250[/td]
[td]260[/td]
[/tr]
[/table]



*********************************************************************************************************************************************************



TABLE B.
[table="width: 500, class: grid, align: center"]
[tr]
[td]TAGS[/td]
[td]Weighted Price[/td]
[/tr]
[tr]
[td]LCBP[/td]
[td]#Value[/td]

[/tr]
[/table]

So if you take a look, this is an extrapolation of a huge sheet called "SalesDataEntry" in my workbook called RALEX2015.

TABLE B is where I want the average price to go. The Tags or the 4 letter product codes are under the name _TAGS1 and are pulled from TABLE A. This name range pulls all of the information in TABLE A...as you can see they show a specific product and the price for a particular time.

So, what I want to do, is get an Average of all the prices in a date range. The dates that are being pulled from are in the workbook called "Interactive Lumber Graph" and the sheet I would be pulling from is called "Lumber1 History" the dates have a name range but it isn't working "Dates1".

So, to summarize what I need:
1) I need excel to find the 4 letter tag number from TABLE B and MATCH it with TABLE A "_TAGS1" is the name range of all the tags in Table A.

2) I need excel to find ALL of the date ranges in between a certain date range parameter, which I can't get to pull MY name range which is "Dates1" and therefore, I am just pulling down 1000 cells in that sheet "lumber1 History" in the "Interactive Lumber Graph"

3) I then need excel to average all of the prices in the date range but also, for the specific TAG number

Please let me know if you have any questions.

 

[ndsutherland]

Try

=AVERAGE(IF(Table A[Column1]>=StartDate,IF(Table A[Column1]<=EndDate,OFFSET(Table A[Column1],,MATCH(Table B[@TAGS],Table A[#Headers],0)-1)))) confirmed with Ctrl+Shift+Enter


http://1drv.ms/1xdGOCy