I am creating a table of service usage by month, i’ll be adding the most recent month as the first column going forwards. So for now the first column is July and next month I’ll input August in front of it and bump the rest down.
I want to create a formula that shows me the last 6 months average for that table. I can create this formula but then when I add in the most recent month at the start the formula moves down and does not stay on the cells (rather stays with the chosen columns). In hoping I don’t have to manually update this formula to include the most recently added column each month is there a way to ‘lock’ a formula in place at all? Or any other solutions - perhaps linking a live excel doc and doing it that way?
You can use a date range control which you can reference in the formula. In that way only the rows which fall with their date between this range will be picked up and calculated.
Months should be ROWS and NOT columns!.
If you have for example 100 metrics and for each of these metrics you want to calculate things (based on the monthly amounts), you should use the subitem feature. Metric A as parent item with month 1, month 2 etc under as subitem. Same for Metric B and so on.
In this way things stay scalable, you can have as many metrics you track and their respective months under the same table plus you keep columns to a minimum. You can automate adding rows but you CANNOT automate adding columns!