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...
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!
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.