+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    10-19-2021
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    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.

    Please help.

    Many thanks!
    Attached Files Attached Files
    Last edited by jpowellzy; 10-19-2021 at 10:03 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    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.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    10-19-2021
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    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. #4
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    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. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    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. #6
    Registered User
    Join Date
    10-19-2021
    Location
    London, England
    MS-Off Ver
    365
    Posts
    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. #7
    Registered User
    Join Date
    10-19-2021
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

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

    Multiple years

  8. #8
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    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. #9
    Registered User
    Join Date
    10-19-2021
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

    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.

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

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

    Please add your Excel version to your profile https://www.extendoffice.com/documen...d-version.html

  11. #11
    Registered User
    Join Date
    10-19-2021
    Location
    London, England
    MS-Off Ver
    365
    Posts
    6

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

    Have found it as MS365

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dynamic Offset by detecting blank cells in between values
    By mart.razal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-01-2021, 11:46 AM
  2. Replies: 4
    Last Post: 04-02-2015, 01:33 PM
  3. Replies: 3
    Last Post: 09-01-2014, 05:56 PM
  4. Dynamic / Named Print Range To Include Another Range of Cells
    By thekrakenwakes in forum Excel General
    Replies: 0
    Last Post: 07-25-2014, 07:52 AM
  5. [SOLVED] find cells with values that fall between dynamic start and end value, color offset (-1,0)
    By ThaGonz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2014, 11:56 PM
  6. Average over a dynamic range of values in a different worksheet
    By MummyB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-30-2012, 10:41 AM
  7. Replies: 5
    Last Post: 05-26-2006, 04:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1