Error when averaging empty & non empty cells

**simonlblea** · 07-08-2013, 12:53 PM

Hi

I have been helped on another post with a problem averaging only unique figures from a column. (I've only started this new one so new viewers don't have to wade through all my posts in the old one)

I am using the array formula =AVERAGE(IF(FREQUENCY(A2:A31,A2:A31),A2:A31)) which has worked brilliantly but only if there are less than two blank cells in the list.

My problem is that I will be starting each month with a blank list and adding to it each day so the formula as is will only work on the penultimate day of the month.

Could someone please help? I am using Excel 2013.

Thank you.

**AlKey** · 07-08-2013, 01:22 PM

Try =SUBTOTAL(101,B4:B33)

**Tony Valko** · 07-08-2013, 03:02 PM

Here's the other thread:

http://www.excelforum.com/excel-form...lues-only.html

And here's the array formula** updated for the different ranges:

=AVERAGE(IF(FREQUENCY(IF(B4:B33<>"",MATCH(B4:B33,B4:B33,0)),ROW(B4:B33)-ROW(B4)+1),B4:B33))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**simonlblea** · 07-09-2013, 04:03 AM

Thank you Tony

You've cracked it! Great job!

**Tony Valko** · 07-09-2013, 09:04 AM

You're welcome. Thanks for the feedback!

Error when averaging empty & non empty cells

LinkBack

Thread Tools

Rate This Thread

Display

Error when averaging empty & non empty cells

Re: Error when averaging empty & non empty cells

Re: Error when averaging empty & non empty cells

Re: Error when averaging empty & non empty cells

Re: Error when averaging empty & non empty cells

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions