Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove error value when formula exists for empty cells | Excel Worksheet Functions | |||
continuing formula when cells are empty | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
quick way to copy-paste a formula linked to cells in another file | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel |