# Array formula to ignore blank cells

1. ## Array formula to ignore blank cells

Hi! I've been perusing a spreadsheet in Excel 2003 I've been working on with lots of array formulas to calculate averages based on two criteria. I thought my formulas were working until I discovered my averages seemed abnormally low. I checked this using a filter on the data and confirmed it.

I have several cases where there is no data to average for a cell, but the cell is counted since it meets the two if criteria. I believe the array is then counting this as a zero and averaging accordingly.

Is there an easy way to write the array formula to ignore the blank cells?

Here, for example, is what I am doing now:

=AVERAGE(IF(SUMMARY!\$AF\$2:SUMMARY!\$AF\$300=\$A3,IF(SUMMARY!\$BK\$2:SUMMARY!\$BK\$300=\$A\$2,SUMMARY!BG\$2:SUMMARY!BG\$300)))

I want the cell to go to the Summary tab, check column AF and BK and then if the two criteria are met, average the data in cell BG. However, there are one or two rows in BG that are blank in some cases. How do I write the formula to exclude those cells?

Thanks for your help. I can attach a spreadsheet if necessary.  Register To Reply

2. ## Re: Array formula to ignore blank cells

Try:

=AVERAGE(IF(SUMMARY!\$AF\$2:\$AF\$300=\$A3,IF(SUMMARY!\$BK\$2:\$BK\$300=\$A\$2, IF(SUMMARY!BG\$2:BG\$300<>"",SUMMARY!BG\$2:BG\$300))))  Register To Reply

3. ## Re: Array formula to ignore blank cells

Bless you! that was exactly what I needed. I did all kinds of searches and was close as I saw several references to the <> addition to the formula, but no idea how to add it.

You've saved my life. Thank you! I shall give you high marks!  Register To Reply

4. ## Re: Array formula to ignore blank cells

Hi!

I have been trying in vain to figure out how to modify the following array formula in order to make Excel ignore blanks in the 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 below 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').

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

Kind Regards,
James.  Register To Reply

##### Users Browsing this Thread

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