a formula which calculates empty cells

[jeff lebowski]

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;
http://briefcase.yahoo.com/[email protected] 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,

 

[Biff]

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" <[email protected]>
wrote in message
news:[email protected]...