Forum Discussion
Find last value in a row (that will change) and calculate weighted average
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...
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.)
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.
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.
- 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!
