a formula which calculates empty cells

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,

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]...
>
> 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,
>
>
> --
> 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
>