Would someone be able to help me make a formula that will calculate the last n cells (in this case, I want n = 12) within the same row. I'd like it to be rolling, so that I can add future data/columns and have the total still calculate the last 12. There will not always be data, so some cells may have 0 or blank and I'd like it to still count that as a cell towards the n count, using a value of zero. I've attached a workbook example.
For example using my attached sheet, row 3: I currently have data in columns B through N. I have the formula for the total in column O. I manually selected the last 12 cells to get the total that I have currently. But I'd like to replace that simple formula that will automatically select the last 12 columns, even if I had a new column before it. For example, right now I have summed up Feb-13 to Jan-14, but next month I will be adding in a new column in between Jan-14 and the total column, for Feb-14. I'd like the total, which would now get pushed to column P, to update the sum to be the new last 12 cells.
And then I need the same thing done for column P: where i'm taking an "average" for the last 12 cells of data (from my months). I'd like it to be rolling average of last 12 cells. In this case however, I want it to ignore 0's and not apply it to the average.
At first I was just using my simple sum formula, and then manually updating which cell it started with and then double clicking the formula to apply to the rest of the sheet: but I have some color formatting separating my customers data, so when i double click it only applies it down to the next customer data (because it sees the different style formatting) And if I drag down to the bottom, I lose my color formatting that highlights when each customer starts.
Any help would be greatly appreciated!! Thanks.
sample_rolling12month_count.xlsx
Bookmarks