Hello,
I would like to calculate a rolling average of monthly data to calculate quarterly averages (the year split into four). See table of data below. I want to calculate the four quarters of 2017 data (Quarter 1= January-March/ Q2= April-June etc.). However, if a cell displays 'DL' I do not want to calculate the average for that quarter. I would like that calculation to display 'DL' also.
The formulae I have come up with is something like this: =IF(AND(ISNUMBER(OFFSET(C$3,(ROW()-3)*3,0,3,1))),AVERAGE(OFFSET(C$3,(ROW()-3)*3,0,3,1),"DL"))
This is because I am offsetting the results. But instead of displaying 'DL' when I want to, it displays FALSE or #DIV/O!.
I am also using OFFSET between multiple tabs.
Please help.
Many thanks!
Bookmarks