# Array formula to ignore blank cells

1. ## 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.

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. ## 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)))

3. ## 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.

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

#### 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