Results 1 to 32 of 32

formula (array or otherwise) to average data in one column in different size blocks

Threaded View

  1. #25
    Registered User
    Join Date
    10-15-2016
    Location
    Taipei Taiwan
    MS-Off Ver
    2010
    Posts
    11

    Re: formula (array or otherwise) to average data in one column in different size blocks

    Hi again,

    I have been away for a few weeks. I appreciate the assistance and have managed to 'combine' all of the suggestions from Jason/John and AliGW to come up with a formula that appears to work. The one issue with Johns 'formula' was the value '5' . This is fine if there are 5 rows or cells of data in the block. However if this number changes to 3 or even 1 then that formula will not work. Nevertheless I have adapted this formula with the following (pasted into B1).

    =IFERROR(AVERAGE(OFFSET(N2,MATCH("1",N2:N200,0),,(MATCH("2",N2:N200,0)-MATCH("1",N2:N200,0)-2))),"")

    The blocks of data to consider are in column 'N'. I have deliberately left out '$' because in my actual spreadsheet I need to copy column B to other parts of the spreadsheet. Also the above formula in B1 cannot be copied down the column because the markers "1", "2" etc, need to change in each row. Hence the formula in B2 will be:

    =IFERROR(AVERAGE(OFFSET(N2,MATCH("2",N2:N200,0),,(MATCH("3",N2:N200,0)-MATCH("2",N2:N200,0)-2))),"")

    Naturally this gets quite tedious if there a 'millions' of rows but fortunately I have a maximum of 20 rows so the task is not too bad. The formula may not be elegant and I am sure there are folk out there who could modify it in case one does have 'millions' of rows.

    I attach the actual part of the spreadsheet

    Thanks again

    Chris
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. average certain blocks of cells VBA
    By ferday in forum Excel General
    Replies: 6
    Last Post: 06-08-2016, 12:47 AM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. Replies: 13
    Last Post: 10-08-2014, 08:35 AM
  4. [SOLVED] Mixing Blocks of Data from 2 Columns into 1 Column, with conditions
    By jocund.ky in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2012, 06:40 PM
  5. Using macros to identify blocks of data and summarise those blocks
    By gophbeav in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2012, 04:35 AM
  6. Clubbing blocks of Data for two entities in a Column Chart
    By e4excel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-12-2009, 10:50 AM
  7. Replies: 1
    Last Post: 03-26-2007, 07:01 PM

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