+ Reply to Thread
Results 1 to 3 of 3

Array formula to ignore blank cells

  1. #1
    Registered User
    Join Date
    09-22-2012
    Location
    Cheltenham
    MS-Off Ver
    Excel 2003
    Posts
    5

    Array formula to ignore blank cells

    Hi!

    I am trying to figure out how to modify the following array formula in order to make Excel ignore blank cells when averaging:

    =AVERAGE(IF(MOD(ROW(OFFSET(Data,Start-1,0))-ROW(OFFSET(OFFSET(Data,Start-1,0),0,0,1,1)),EveryN)=0,OFFSET(Data,Start-1,0),FALSE))

    The modification in the thread at http://www.excelforum.com/excel-form...ank-cells.html looks very simple but I can't figure out how to apply it to the above formula. I am trying to average every nth data value (specified in cell 'EveryN') in the list 'Data' given a certain start position (specified in cell 'Start') - as per the example at http://www.cpearson.com/excel/EveryNth.aspx.

    Please could you help me out? I would be very grateful for your advice.

    Kind Regards,
    James.

    PS I replied to the thread posted above but it doesn't seem to show up on the main page so I am starting a new topic.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Array formula to ignore blank cells

    Hello James,

    Personally I wouldn't use that formula as it can actually include cells which aren't in the range Data, e.g. if I define data as B2:B14 and Start as 4 and EveryN as 3 that should average B5, ,B8, B11 and B14.......but it actually includes B17 too! (OFFSET is moving the start of data but keeping the height the same so it goes beyond the original definition)

    Having said that, if you don't mind that issue (for example if all the cells further down are blanks) you can adjust it to ignore blanks with this formula:

    =AVERAGE(IF(MOD(ROW(OFFSET(Data,Start-1,0))-ROW(OFFSET(OFFSET(Data,Start-1,0),0,0,1,1)),EveryN)=0,IF(OFFSET(Data,Start-1,0)<>"",OFFSET(Data,Start-1,0))))

    ...do you really need to adjust the start cell? If not then this formula will average every EveryNth Cell in Data starting with the first cell of Data (assuming Data is a single column), ignoring blanks

    =AVERAGE(IF(MOD(ROW(Data)-MIN(ROW(Data)),EveryN)=0,IF(Data<>"",Data)))
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-22-2012
    Location
    Cheltenham
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Array formula to ignore blank cells

    Thank you very much for your help - that has solved the problem!

    I have been setting up a spreadsheet which will collapse monthly time series datasets to monthly averages, hence needing to average every 12th row and create an offset for each month. I couldn't get past the issue with blank cells so it is a relief that it is sorted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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