Use Formula taking reference of a particular cell across any time period
Hi - Please help me how I can achieve the below expectation using a formula:
- Say, I have 10 periods: 6 periods in past and 4 periods in future (i.e. the 7th period is the current period).
- I calculate the mean of the first 6 periods (past periods) and store the same in the current period (say line item "Mean").
- Now, I want to calculate the deviation of the actuals (values in the past) from the mean value.
- But mean value is present only in the current period.
How can I do 'Actuals - Current period mean value' across each time period in the past? The excel equivalent of this is using the dollar sign for a cell value (so the cell value of mean is fixed).