Summary formula help

I need help with formulas counting dates.

I have open dates and close dates. I need to know the average, minimum and
maximum time a case is open by year. I have figured out the array for the
total period (2002-2006), but can't break it out the same by individual year.

I also am having a problem getting a field to remain blank if no close date
has been entered. Currently it reports 01/00/00 for blanks.

Any/all help appreciated!

Your description does not tell me how to account for a case that is started
in one year and closed in the next. These will only work if started and
closed in the same year, but are easily adaptable.

Assuming start date in column B, close date in column C, headers in Row 1,
and cases down to row 250.
I would type in say column Z, in Z1 1/1/2002, n Z2 1/1/2003, in Z31/1/2004
and so on up to 1/1/2007 just to simplify the formula.

Average for 2002
=sumproduct(--($C$2:$C$250>0),--($B$2:$B$250>=Z1),--($C$2:$C$250<Z2),--($C$2:$C$250-$B$2:$B$250+1))/sumproduct(--($C$2:$C$250>0),--($B$2:$B$250>=Z1),--($C$2:$C$250<Z2))
copydown for later years

Min for 2002 (in say AB1)
=MIN(IF($C$2:$C$250>0,IF($B$2:$B$250>=Z1,IF($C$2:$C$250<Z2,$C$2:$C$250-$B$2:$B$250+1))))
This in an array formula, you must commit with control+shift+enter
Max (say in AC1) is the same as min except trade out the MIN for MAX

When you copy down the min and max functions, you must not paste over the
cell you copied, just select the rows below to paste.

If you want this adjusted for how to account for a case that is started in
one year and closed in the next, just respond to this thread and I'll see it
today or tomorrow.



"Kathryn J Bittman" <[email protected]> wrote in
message news:[email protected]...
>I need help with formulas counting dates.
>
> I have open dates and close dates. I need to know the average, minimum and
> maximum time a case is open by year. I have figured out the array for the
> total period (2002-2006), but can't break it out the same by individual
> year.
>
> I also am having a problem getting a field to remain blank if no close
> date
> has been entered. Currently it reports 01/00/00 for blanks.
>
> Any/all help appreciated!

Bob,
Thanks for the help. Still not getting it yet, but closer. Does it matter
that all the dates for open are the first of the month and all the close
dates are the last day of the month? I am trying on use the year without
month specification in the Z1 part of your formula. Is that why I am not
getting the result I want?

I will need the help with the latter situation of open this year, but closed
the next.

"Bob Tarburton" wrote:

> Your description does not tell me how to account for a case that is started
> in one year and closed in the next. These will only work if started and
> closed in the same year, but are easily adaptable.
>
> Assuming start date in column B, close date in column C, headers in Row 1,
> and cases down to row 250.
> I would type in say column Z, in Z1 1/1/2002, n Z2 1/1/2003, in Z31/1/2004
> and so on up to 1/1/2007 just to simplify the formula.
>
> Average for 2002
> =sumproduct(--($C$2:$C$250>0),--($B$2:$B$250>=Z1),--($C$2:$C$250<Z2),--($C$2:$C$250-$B$2:$B$250+1))/sumproduct(--($C$2:$C$250>0),--($B$2:$B$250>=Z1),--($C$2:$C$250<Z2))
> copydown for later years
>
> Min for 2002 (in say AB1)
> =MIN(IF($C$2:$C$250>0,IF($B$2:$B$250>=Z1,IF($C$2:$C$250<Z2,$C$2:$C$250-$B$2:$B$250+1))))
> This in an array formula, you must commit with control+shift+enter
> Max (say in AC1) is the same as min except trade out the MIN for MAX
>
> When you copy down the min and max functions, you must not paste over the
> cell you copied, just select the rows below to paste.
>
> If you want this adjusted for how to account for a case that is started in
> one year and closed in the next, just respond to this thread and I'll see it
> today or tomorrow.
>
>
>
> "Kathryn J Bittman" <[email protected]> wrote in
> message news:[email protected]...
> >I need help with formulas counting dates.
> >
> > I have open dates and close dates. I need to know the average, minimum and
> > maximum time a case is open by year. I have figured out the array for the
> > total period (2002-2006), but can't break it out the same by individual
> > year.
> >
> > I also am having a problem getting a field to remain blank if no close
> > date
> > has been entered. Currently it reports 01/00/00 for blanks.
> >
> > Any/all help appreciated!
>
>
>

This should work based on those specs.
Post some examples of the date format you are using or e-mail me a sample
spreadsheet.
Just remove the "_removethis_" from my email address.


"Kathryn J Bittman" <[email protected]> wrote in
message news:[email protected]...
> Bob,
> Thanks for the help. Still not getting it yet, but closer. Does it matter
> that all the dates for open are the first of the month and all the close
> dates are the last day of the month? I am trying on use the year without
> month specification in the Z1 part of your formula. Is that why I am not
> getting the result I want?
>
> I will need the help with the latter situation of open this year, but
> closed
> the next.
>
> "Bob Tarburton" wrote:
>
>> Your description does not tell me how to account for a case that is
>> started
>> in one year and closed in the next. These will only work if started and
>> closed in the same year, but are easily adaptable.
>>
>> Assuming start date in column B, close date in column C, headers in Row
>> 1,
>> and cases down to row 250.
>> I would type in say column Z, in Z1 1/1/2002, n Z2 1/1/2003, in
>> Z31/1/2004
>> and so on up to 1/1/2007 just to simplify the formula.
>>
>> Average for 2002
>> =sumproduct(--($C$2:$C$250>0),--($B$2:$B$250>=Z1),--($C$2:$C$250<Z2),--($C$2:$C$250-$B$2:$B$250+1))/sumproduct(--($C$2:$C$250>0),--($B$2:$B$250>=Z1),--($C$2:$C$250<Z2))
>> copydown for later years
>>
>> Min for 2002 (in say AB1)
>> =MIN(IF($C$2:$C$250>0,IF($B$2:$B$250>=Z1,IF($C$2:$C$250<Z2,$C$2:$C$250-$B$2:$B$250+1))))
>> This in an array formula, you must commit with control+shift+enter
>> Max (say in AC1) is the same as min except trade out the MIN for MAX
>>
>> When you copy down the min and max functions, you must not paste over the
>> cell you copied, just select the rows below to paste.
>>
>> If you want this adjusted for how to account for a case that is started
>> in
>> one year and closed in the next, just respond to this thread and I'll see
>> it
>> today or tomorrow.
>>
>>
>>
>> "Kathryn J Bittman" <[email protected]> wrote in
>> message news:[email protected]...
>> >I need help with formulas counting dates.
>> >
>> > I have open dates and close dates. I need to know the average, minimum
>> > and
>> > maximum time a case is open by year. I have figured out the array for
>> > the
>> > total period (2002-2006), but can't break it out the same by individual
>> > year.
>> >
>> > I also am having a problem getting a field to remain blank if no close
>> > date
>> > has been entered. Currently it reports 01/00/00 for blanks.
>> >
>> > Any/all help appreciated!
>>
>>
>>