average two columns using lookups

[jgedwardsv]

Hi all,

First off, I feel like I'm on here all the time, but everyone is just so helpful!

I am trying to create a column in a table that will consolidate all the key information in the rest of my report. Where I am having trouble is with the average column. I am trying to, with one formula, show the average differential between two arrays. The first array is always fixed, the second will depend on the first column of my table. Essentially what I want to do is take the fixed column number in the first row, subtract it from the lookup column first row, then do the same down the remaining 14 rows and average the result.

Can I do the lookup part and subtraction part wrapped in the average formula? Thus far the subtraction part only seems to pull the first number of the fixed array.

I can provide the sample spreadsheet if that helps as I'm sure I am not explaining this properly.

Thanks for all the help!

John

 

[jgedwardsv]

https://www.dropbox.com/s/ddptn98no1b1kgs/TDFWorksheetTemplate.xlsx?dl=0

That is the spreadsheet - I am trying to do the calculations on Sheet 1 (I haven't named it yet) and the data should be pulled from the Participant Analysis table. I want to lookup the name of the TDF Series and then pull the values from the corresponding column and then subtract the values in the AverageEquity Column then average. The sheet TDF Analysis shows line by line (I haven't finished copying down the formulas for all of them yet) of what I am trying to do.