ExcelBanter - a formula which calculates empty cells

Hi!

Try this:

Enter this formula in A3:

=IF(A2<"","",1)

Enter this formula in B3 and copy across to T3:

=IF(B2<"","",IF(AND(B2="",A2=""),"",MAX($A3:A3)+1 ))

Then, to get the average number of empty cells:

=COUNTBLANK(A2:T2)/MAX(A3:T3)

Returns 2.5

You may want to tweak the average formula just in case there are no EMPTY
cells otherwise the formula will return a #DIV/0! error.

Biff

"jeff lebowski"
wrote in message
news:jeff.lebowski.2ayezh_1152912008.4093@excelfor um-nospam.com...

Hello, I have a row in which some cells have numbers and some cells are
blank. To show an example of what this spreadsheet looks like have
posted a jpg of it here;
It is example 1.
This is just an example. My rows contain many more cells.
What I am trying to calculate is the average # of consecutive blank
cells in the row. In my example the answer would be 2.5 The total # of
blank cells is 10 and there are a total of 4 areas of consecutive blank
cells. 10/4=2.5 My question is what formula or combination of formula
would I use for this calculation? I have been able to use the count
formula (example #2 and #3 in the weblink) but, I can't figure out how
to have excel tell me there are 4 areas of consecutive blank cells in
this example.

thank you for for time and consideration,

--
jeff lebowski
------------------------------------------------------------------------
jeff lebowski's Profile:
http://www.excelforum.com/member.php...o&userid=36376
View this thread: http://www.excelforum.com/showthread...hreadid=561641