Sep 22 2021 10:37 PM
Sep 22 2021 10:37 PM
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!
Sep 23 2021 06:30 AM
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."
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.)
Sep 23 2021 06:50 AM
@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.
Sep 23 2021 07:30 AM
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.
Sep 23 2021 05:46 PM
Sep 23 2021 06:12 PM
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.
Sep 24 2021 03:06 AM - edited Sep 24 2021 03:07 AM
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.
Sep 24 2021 06:19 PM
Sep 25 2021 03:05 AMSolution
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)
(Column(H)-6)*12-11=2 = ($Z$2:Width)
And so on...