Find last value in a row (that will change) and calculate weighted average

[Felicity123]

I have an extremely large data set so I think that using the offset function will be too time-consuming.  The columns are dates and will have new values added each month.

The rows will change position as they are being ranked each month.

Ultimately I am trying to put together a formula that will calculate a weighting value for each row.  This will be represented by a weighting value (W1) applied to the % change in average values for the last 12 months (P1) as compared to the 12 months before that (P2) combined with a weighting value (W2) applied to the % change in average values for the period P2 as compared to the average value for the 12 months prior to that (P3) etc, etc

I need help with all of this please!

[#2]

@Felicity123 

Forty views as of this writing, no replies. I suspect it's because you've given a meaty description but not enough clarity, some confusion (e.g., "The columns are dates and will have new values added each month. The rows will change position as they are being ranked each month.":( is the entire data set comprised of dates, all columns? 

In any event, you would go a long way toward helping us help you if you were to attach either the actual spreadsheet or (if the actual contains confidential data) a reasonable mockup of it. Not an image; an actual spreadsheet that represents (or is) the actual data and the actual array. It needn't be all the rows, but it should be an adequate representation of the full reality. 

Then, supplement that with a specific description of which values is it that are being assessed in terms of monthly change, trailing 12 month averages, etc.

(Given the interest in trailing 12 month figures, I'm inferring that this might have to do with stock market data...but that is just a guess; if that's the case, I have a spreadsheet where I track trailing 12 month results, and can tell you it will be fairly straight forward to resolve your concerns.)

[#3]

@mathetes Thanks for your comments. This is my first post so am grateful for your help.

You are correct in your assumptions regarding trailing data.  What I am trying to do is sort rows of data to show which are demonstrating the greatest upward trend (exponential rather than linear).  I have manually applied weightings but this still doesn't seem to weed out items with a seasonal bias.  

Am grateful for any solutions!

I have attached an example sheet.

[#4]

@Felicity123 

Well, from the looks of that spreadsheet, you're quite sophisticated in your use of Excel. In fact, your reference to "exponential rather than linear" suggests to me that you're far more sophisticated in math and statistics than many who post here (and more in stats than I am, for that matter).

The moving trailing 12 month average is easy to determine for any given stock at any point in time. For example, here's the formula I entered in cell Y103, pertaining to st100 in your spreadsheet. I then copied that formula across the columns...... Here's the formula, with relative references, so they always are showing the average of the prior 12 months. =AVERAGE(N101:Y101)

Then (as additional evidence of your level of sophistication) I noticed that you've implemented custom formatting to the numbers, rounding (it appears) to the nearest $M, which is not as granular (I wouldn't think) as you might want....

Anyway, I find it hard to believe that you don't know how to do a trailing twelve month formula-- and am frankly stymied as to exactly what the help is that you need. It may well be that some others among the experts here have more experience than I with this kind of financial analysis, and I'll defer to them. But if you can be patient with me, just humor me by telling me how (in words, referring to the data too) you'd assess exponential trend......etc.

[#5]

Thanks for your kind comments and for your solution. Unfortunately that is not what I am needing. I need the value to be referenced automatically and so that it can go in a formula with the logic - average(last value:last value minus 12mths) (or other period as I determine).
I can do this by inserting a cell and having it reference the cell for the value, but don't wish to as I want to use the value in a weighted averaging formula and will end up having to add another 10 or so columns to achieve the end result. The logic of the end result is something like - weighted average of last 3 mths, weighting; average of 3mths prior to that, weighting; average of 6 mths prior, weighting etc etc.

Because the actual sheet has over 50K values I also want to minimise the complexity of the formulas so as to minimise calculation time.

Regarding my experience with excel: I have used it for many years at an advanced level but then changed jobs and haven't used it for about 10 years so am very rusty! Ditto for my statistical analysis skills.

In the sheet I have used very crude measures to try to determine the fit of the data to an exponential line. A moving average is OK if you have a clear pattern but as you can see from the sparklines that is not the case in my data. So the best I can do (that I'm aware of) is to apply some exponential smoothing. Col C is measures alpha which is the exponential smoothing factor. If you sort by this column (largest -smallest) you will see that for the most part the data follows the exponential trend. My problem is that it doesn't sort it as well as I'd like so I am experimenting with other factors overlaying that sort, hence the formula I am trying to construct.

I'm certainly open to any alternative solutions on this problem!

[#6]

@Felicity123 

This would no doubt be a lot easier if we were sitting down face-to-face, because we could exchange questions and answers, observations and clarifications in just a few sentences at a time. As it is, one of the short comings of communicating through exchanged long messages is that we can't stop one another mid-stream and say, "Stop, please explain a little more what you mean by......."

I'm not at all surprised that what I wrote was NOT what you were seeking. It was totally too obvious.

But I still don't understand what you're working so hard to describe. And frankly it sounds as if you're still working to define it for yourself. If we only had a white board also, in addition to being face-to-face. We could take turns at the white board drawing equations, incorporating new variables in them.....

You also know what the underlying reality is that the data are representing. I can only surmise. And surmising on top of vagueness (as I experience it, not as you transmit it) ain't all that productive.

So I wish you well. My guess is that your rustiness will turn to polish quickly enough....after which you may want to join the ranks of people here on this site answering questions from others. I retired nearly 20 years ago, discovered this site 23 months ago through seeking the answer to an Excel question of my own...and have enjoyed answering (or trying to) questions ever since.

[#7]

@Felicity123 

I guess you need a way to dinamically develop an array calculation.
Considering you are on G column, you can play with the following AVERAGE with dinamic range using OFFSET formula.

=AVERAGE(OFFSET($M2,0,(COLUMN()-6)*12-11,1,12))

[#8]

Thanks Juliano. I think you might be on the right track with this.
Could you please explain to me how this part of the formula works? (COLUMN()-6)*12-11,1,12)

[#9 · Accepted solution]

@Felicity123 

OFFSET(StartingReference,0,ColumnsToDisplace,Height,Width)
If you are on column G, means that you will need to displace only 1 column from your StartingReference and then 12 columns of width (12 months range).
(Column(G)-6)*12-11=1 = ($N$2:Width)

$N$2:$Y$2

(Column(H)-6)*12-11=2 = ($Z$2:Width)

$Z$2:$AK$2

And so on...

[#10]

thank you!