Calculate average only if all cells include values. Also use OFFSET for dynamic range.

1. Calculate average only if all cells include values. Also use OFFSET for dynamic range.

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.

Many thanks!

2. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Please see yellow banner at top of page on how to attach a sample workbook. Ensure you have manually-calculated results.

3. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Have posted a sample. I need the OFFSET function as my calculations are a dynamic range over many years and many sites. Thanks

4. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Try this: =IF(ISNUMBER(MATCH("DL",OFFSET(C\$3,ROWS(G\$4:G4)*3,,-3),)),"DL",AVERAGE(OFFSET(C\$3,ROWS(G\$4:G4)*3,,-3)))

5. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Will all the calculations pertain to the same year, or will there be different years?
BTW OFFSET being a volatile function, better to avoid it using Excel Tables or INDEX/MATCH combinations

6. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

That works for my original sample. I tried to work out for my actual work and I can't . I don't understand the bit after 'ROWS'. I have uploaded a new sample as I am aware it may be because I am offsetting from one tab to the next. Please can you check how this would work for the sample where I have to offset the results into a new tab. Many thanks for help!

7. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Multiple years

8. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Not clear. If for 2017 Q2 you have only the months of April and May, then what's the quarterly average?
Do you average across 2 or 3 months?

9. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

I have updated the sample spreadsheet with more representative data. Please see new sample. But for old sample- Yes I am not bothered about blanks, in this instance. If there is a blank just average whatever results there are (1, 2 or 3 months). What I do not want, is to average if there is a 'DL'. Then I want the results to display 'DL' if one, two or three months display 'DL' in the first tab.

11. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Have found it as MS365

12. Re: Calculate average only if all cells include values. Also use OFFSET for dynamic range.

Many of the cells on Sheet1 of the file reference an external file to which we don't have access:
'S:\Public Protection Service\Environmental Monitoring\Air Quality\National Air Quality Plan\Monitoring\Quarterly Reports\Process Documents\M1 - Air Quality\[Quarterly Master.xlsx].
Therefore when I select Enable Editing the numbers under the sites become #VALUE! errors.
Also some of the months in column B are text (Nov 17) while others are actual dates 12/1/2017.
If we could see a sample file that only included the numeric values in C6:I25, instead of formulas referencing the external file, and in which all months in column B were dates we might be in a better position to help.
I am thinking that as you are using the 365 version of Excel it may be appropriate to utilize Power Query and/or Power Pivot if the use of those is acceptable.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)