Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to calculate the average number of consecutive cells
Hello!
I have a column in Excel consisting of 1's and 0's. I need to calculate the average size of consecutive cells (groups of cells) containing 1's. For example, the average size of consecutive cells containing the number 1 in the following array is 3 (i.e., (2+4+3+3)/4 = 3): 1100111100000011100011100000 Can someone *please* suggest a formula to do this? Thanks in advance! Ilford |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to calculate the average number of consecutive cells
hi, Ilford !
I have a column in Excel consisting of 1's and 0's. I need tocalculate the average size of consecutive cells (groups of cells) containing 1's. For example, the average size of consecutive cells containing the number 1 in the following array is 3 (i.e., (2+4+3+3)/4 = 3): 1100111100000011100011100000 Can someone *please* suggest a formula to do this? assuming your array of numbers in [A2:A29] and you can use [A30] as the "end-cell" (blank or whatever BUT 1's ) -?- =sum(a2:a29)/sumproduct(--(a2:a29=1),--(a3:a30<1)) if any doubts (or further information)... would you please comment ? hth, hector. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to calculate the average number of consecutive cells
Thank you so much, Hector! The formula works perfectly and is easily modified to work with my data. Best wishes, Ilford |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Formula for Weighted Average in non-consecutive cells | Excel Worksheet Functions | |||
Average non-consecutive cells excluding zero | Excel Worksheet Functions | |||
Average Non-consecutive Cells | Excel Discussion (Misc queries) | |||
Calculating the Average for non consecutive cells using custom for | Excel Discussion (Misc queries) | |||
Calculate average pay from highest five consecutive years | Excel Programming |