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