I have a set of numbers (A) that can be graphed out as a chart. This chart is strongly related to another set (B) and I want to use it to predict B. So, for example, I know sales history for wombats and I know that for every wombat I sell, I sell a certain related number of fish sticks four weeks from now. With the last 4 weeks of data wombat data, I can predict the next 4 weeks of fish sticks data. The relationship between these is static, week to week, though the actual sales numbers change.
Say that I have calculated that the average height of the two graphs is (for example) 4 and 1 relatively, and the standard deviation 0.6 and 0.2 respectively for all examples. I can translate A into an approximation of B through the following transform:
heightFactor = 1 / 4
deviationFactor = 0.2 / 0.6
(A - avg(A)) * deviationFactor + avg(A) * heightFactor = B
I’d like to be able to generate a linear transformation (V) such that I can multiply it by this week’s A to predict next week’s B, using the constant, known heightFactor and deviationFactor.
Obviously, I could just run the formula above against it, but having a single linear transformation is more useful. The number of elements (n) in A and B is constant and known (e.g., 4 in the wombat/fish sticks example).
Is it possible to calculate a constant, predetermined V such that:
It’s really not clear what you want, but AV = B is a special case of a linear relation. It is a proportional relation. A linear relation would be AV + U = B.
If you truly believe that B is proportional to A, then you can compute V by running a regression of B on A and suppressing the intercept.
You can do this in Excel using Linest
LINEST({your B’s}, {your A’s}, FALSE, TRUE or FALSE)
The 1st “False” tells it to suppress the intercept. The final true or false tells Excel whether or not you want additional statistics like the R-squared.
If the sale of wombats is zero for some week, is the corresponding sale of fish-sticks going to be zero also (for the corresponding time-period, which I gather would be 4 weeks later)?
(Are the fish-sticks for the wombats to eat?)
If 0 wombat sales leads to 0 fish-stick sales, then your graph includes the origin (0, 0) and the linear transformation is A * V = B, with no U term. This is also called a “homogeneous” relation.