Hi All,
I am working with a database that needs the rolling total of the last 3 months. For example, let say we are in June. One column will Sum the total of Mar, April, May. Another column for Feb, March, April. And the last one being Jan, Feb, March.
I have figured out how to calculate the rolling total if there is a full set of data (Jan to Dec). Using =SUM(OFFSET($A3,0,COUNT($B3:$M3)-2,1,-3)) ; =SUM(OFFSET($A3,0,COUNT($B3:$M3)-1,1,-3)) ; =SUM(OFFSET($A3,0,COUNT($B3:$M3),1,-3))
How do I calculate the totals if we are in March and the data has not come in for the rest of the months? Ideally I want it to look like something below.
Months
<tbody>
</tbody>
I am working with a database that needs the rolling total of the last 3 months. For example, let say we are in June. One column will Sum the total of Mar, April, May. Another column for Feb, March, April. And the last one being Jan, Feb, March.
I have figured out how to calculate the rolling total if there is a full set of data (Jan to Dec). Using =SUM(OFFSET($A3,0,COUNT($B3:$M3)-2,1,-3)) ; =SUM(OFFSET($A3,0,COUNT($B3:$M3)-1,1,-3)) ; =SUM(OFFSET($A3,0,COUNT($B3:$M3),1,-3))
How do I calculate the totals if we are in March and the data has not come in for the rest of the months? Ideally I want it to look like something below.
Months
| Jan | Feb | March | Total of last 3 months | Total of last 3 months minus last month | total of last 3 months minus last 2 months | |
| Hours | 2 | 3 | 1 | 6 | 5 | 2 |
| 2 | 3 | 5 | 5 | 2 |
<tbody>
</tbody>
