Average Number of Days

[kathyb0527]

I have a spreadsheet that has the completion dates of various steps in a process. I need to get the average number of days between each step based on the number of units. I started using formulas, but I've already run into some problems (blank cells the most obvious). I've attached a small sample of the spreadsheet. The actual list will vary each quarter and probably be ~1000 rows. I also have to take the numbers and put them in a table for presentation.

I would prefer to do this in VBA (great learning experience), but I need a shove in the right direction.

Thanks,
Kathyb0527

[Bob Phillips]

Not VBA, but a formula

=AVERAGE(IF(I3:I19<>"",I3:I19))

this is an array formula, so commit with Ctrl-Shift-Enter not just Enter

[kathyb0527]

Thanks XLD. Can a named range be used in an array formula? The number of rows will be changing and I'd like to just use a dynamic named range instead of having to change all of the formulas.

Kathyb0527

[Bob Phillips]

Yes, no problem.