Okay, excel gurus €“ Im wondering if theres any way for excel 02 to return
conditional min and max values and then calculate the difference -- heres
what I have (a small sample of a much larger table imported from a SQL
database):

IRB delivered verdict difference
15781 ?? 26-Jan-06 38743
16746 18-Jan-06 18-Jan-06 0
17127 6-Feb-06 23-Feb-06 17
17127 9-Mar-06 7-Apr-06 29
17127 17-Apr-06 18-Apr-06 1
17692 28-Mar-06 2-May-06 35
18240 15-Feb-06 23-Feb-06 8
18240 13-Mar-06 30-Mar-06 17
18468 8-Feb-06 23-Feb-06 15
18468 4-Oct-06 24-Oct-06 20

Im trying to come up with a way to extract the earliest date in the
€œdelivered€� column and the latest date in the €œverdict€� column for any given
IRB number. If the same number has multiple entries (rows), I then need to
know the absolute earliest and latest dates (and the absolute difference)
over all relevant rows.

From there, I think i can calculate all sorts of useful things, and make
pretty charts and graphs.

Any ideas?

There can be an unlimited number of rows with the same IRB number, if it
matters.

Also, I will eventually be sorting the difference into 15-day sections,
which I'm currently doing with the following kludgy formula and a series of
"countif"s:
=IF(C:C<=15,"<15",(IF(C:C<=30,"<30",(IF(C:C<=45,"< 45",(IF(C:C<=60,"<60","60")))))))

elegant, it's not; suggestions for this part would be most helpful, too....

thanks in advance.

Hi Trevor,

Assuming your data are in columns A2:C11, try the following array formula in
D2:
=MAX(IF(A$2:A$11=A2,C$2:C$11))-MIN(IF(A$2:A$11=A2,B$2:B$11))
and copy down as far as needed. Adjust to suit your actual ranges.

Note that, for a repeated IRB#, the same result will appear on all rows for
that IRB#.

Cheers

--
macropod
[MVP - Microsoft Word]
PS: For a large worksheet, expect a severe performance hit.

"trevoryx" wrote in message
...
Okay, excel gurus €“ Im wondering if theres any way for excel 02 to
return
conditional min and max values and then calculate the difference --
heres
what I have (a small sample of a much larger table imported from a SQL
database):

IRB delivered verdict difference
15781 ?? 26-Jan-06 38743
16746 18-Jan-06 18-Jan-06 0
17127 6-Feb-06 23-Feb-06 17
17127 9-Mar-06 7-Apr-06 29
17127 17-Apr-06 18-Apr-06 1
17692 28-Mar-06 2-May-06 35
18240 15-Feb-06 23-Feb-06 8
18240 13-Mar-06 30-Mar-06 17
18468 8-Feb-06 23-Feb-06 15
18468 4-Oct-06 24-Oct-06 20

Im trying to come up with a way to extract the earliest date in the
€œdelivered€� column and the latest date in the €œverdict€� column for
any given
IRB number. If the same number has multiple entries (rows), I then need
to
know the absolute earliest and latest dates (and the absolute difference)
over all relevant rows.

From there, I think i can calculate all sorts of useful things, and make
pretty charts and graphs.

Any ideas?

There can be an unlimited number of rows with the same IRB number, if it
matters.

Also, I will eventually be sorting the difference into 15-day sections,
which I'm currently doing with the following kludgy formula and a series
of
"countif"s:

=IF(C:C<=15,"<15",(IF(C:C<=30,"<30",(IF(C:C<=45,"< 45",(IF(C:C<=60,"<60","60
")))))))

elegant, it's not; suggestions for this part would be most helpful,
too....

thanks in advance.

Thank you - that seems to work to get the numbers, but do you know of any way
to clean up the repeated values?

"macropod" wrote:

Hi Trevor,

Assuming your data are in columns A2:C11, try the following array formula in
D2:
=MAX(IF(A$2:A$11=A2,C$2:C$11))-MIN(IF(A$2:A$11=A2,B$2:B$11))
and copy down as far as needed. Adjust to suit your actual ranges.

Note that, for a repeated IRB#, the same result will appear on all rows for
that IRB#.

Cheers

--
macropod
[MVP - Microsoft Word]
PS: For a large worksheet, expect a severe performance hit.

"trevoryx" wrote in message
...
Okay, excel gurus €€œ I€„¢m wondering if there€„¢s any way for excel 02 to
return
conditional min and max values and then calculate the difference --
here€„¢s
what I have (a small sample of a much larger table imported from a SQL
database):

IRB delivered verdict difference
15781 ?? 26-Jan-06 38743
16746 18-Jan-06 18-Jan-06 0
17127 6-Feb-06 23-Feb-06 17
17127 9-Mar-06 7-Apr-06 29
17127 17-Apr-06 18-Apr-06 1
17692 28-Mar-06 2-May-06 35
18240 15-Feb-06 23-Feb-06 8
18240 13-Mar-06 30-Mar-06 17
18468 8-Feb-06 23-Feb-06 15
18468 4-Oct-06 24-Oct-06 20

I€„¢m trying to come up with a way to extract the earliest date in the
€œdelivered€� column and the latest date in the €œverdict€� column for
any given
IRB number. If the same number has multiple entries (rows), I then need
to
know the absolute earliest and latest dates (and the absolute difference)
over all relevant rows.

From there, I think i can calculate all sorts of useful things, and make
pretty charts and graphs.

Any ideas?

There can be an unlimited number of rows with the same IRB number, if it
matters.

Also, I will eventually be sorting the difference into 15-day sections,
which I'm currently doing with the following kludgy formula and a series
of
"countif"s:

=IF(C:C<=15,"<15",(IF(C:C<=30,"<30",(IF(C:C<=45,"< 45",(IF(C:C<=60,"<60","60
")))))))

elegant, it's not; suggestions for this part would be most helpful,
too....

thanks in advance.

Hi Trevor,

To do that, you could use the following modification of the array formula :

=IF(COUNTIF(A$1:A2,A2)1,"",MAX(IF(A$2:A$11=A2,C$2 :C$11))-MIN(IF(A$2:A$11=A2
,B$2:B$11)))

With this, the result will appear against the the first IRB# row only.

Cheers

--
macropod
[MVP - Microsoft Word]


"trevoryx" wrote in message
...
Thank you - that seems to work to get the numbers, but do you know of any
way
to clean up the repeated values?

"macropod" wrote:

Hi Trevor,

Assuming your data are in columns A2:C11, try the following array
formula in
D2:
=MAX(IF(A$2:A$11=A2,C$2:C$11))-MIN(IF(A$2:A$11=A2,B$2:B$11))
and copy down as far as needed. Adjust to suit your actual ranges.

Note that, for a repeated IRB#, the same result will appear on all rows
for
that IRB#.

Cheers

--
macropod
[MVP - Microsoft Word]
PS: For a large worksheet, expect a severe performance hit.

"trevoryx" wrote in message
...
Okay, excel gurus €€œ I€„¢m wondering if there€„¢s any
way for excel 02 to
return
conditional min and max values and then calculate the difference --
here€„¢s
what I have (a small sample of a much larger table imported from a SQL
database):

IRB delivered verdict difference
15781 ?? 26-Jan-06 38743
16746 18-Jan-06 18-Jan-06 0
17127 6-Feb-06 23-Feb-06 17
17127 9-Mar-06 7-Apr-06 29
17127 17-Apr-06 18-Apr-06 1
17692 28-Mar-06 2-May-06 35
18240 15-Feb-06 23-Feb-06 8
18240 13-Mar-06 30-Mar-06 17
18468 8-Feb-06 23-Feb-06 15
18468 4-Oct-06 24-Oct-06 20

I€„¢m trying to come up with a way to extract the earliest date
in the
€œdelivered€Â� column and the latest date in the €Å
“verdict€Â� column for
any given
IRB number. If the same number has multiple entries (rows), I then
need
to
know the absolute earliest and latest dates (and the absolute
difference)
over all relevant rows.

From there, I think i can calculate all sorts of useful things, and
make
pretty charts and graphs.

Any ideas?

There can be an unlimited number of rows with the same IRB number, if
it
matters.

Also, I will eventually be sorting the difference into 15-day
sections,
which I'm currently doing with the following kludgy formula and a
series
of
"countif"s:


=IF(C:C<=15,"<15",(IF(C:C<=30,"<30",(IF(C:C<=45,"< 45",(IF(C:C<=60,"<60","60
")))))))

elegant, it's not; suggestions for this part would be most helpful,
too....

thanks in advance.

Hi Trevor,

Following on from the modified formula, you could use something like:
=IF(D2="","",IF(D2=60,"=60","<"&INT(D2/15+1)*15))
for the 15-day segmentation.

Cheers
--
macropod
[MVP - Microsoft Word]


"macropod" wrote in message
...
Hi Trevor,

To do that, you could use the following modification of the array formula
:


=IF(COUNTIF(A$1:A2,A2)1,"",MAX(IF(A$2:A$11=A2,C$2 :C$11))-MIN(IF(A$2:A$11=A2
,B$2:B$11)))

With this, the result will appear against the the first IRB# row only.

Cheers

--
macropod
[MVP - Microsoft Word]


"trevoryx" wrote in message
...
Thank you - that seems to work to get the numbers, but do you know of
any
way
to clean up the repeated values?

"macropod" wrote:

Hi Trevor,

Assuming your data are in columns A2:C11, try the following array
formula in
D2:
=MAX(IF(A$2:A$11=A2,C$2:C$11))-MIN(IF(A$2:A$11=A2,B$2:B$11))
and copy down as far as needed. Adjust to suit your actual ranges.

Note that, for a repeated IRB#, the same result will appear on all
rows
for
that IRB#.

Cheers

--
macropod
[MVP - Microsoft Word]
PS: For a large worksheet, expect a severe performance hit.

"trevoryx" wrote in message
...
Okay, excel gurus €€œ I€„¢m wondering if there€„¢s
any
way for excel 02 to
return
conditional min and max values and then calculate the difference --
here€„¢s
what I have (a small sample of a much larger table imported from a
SQL
database):

IRB delivered verdict difference
15781 ?? 26-Jan-06 38743
16746 18-Jan-06 18-Jan-06 0
17127 6-Feb-06 23-Feb-06 17
17127 9-Mar-06 7-Apr-06 29
17127 17-Apr-06 18-Apr-06 1
17692 28-Mar-06 2-May-06 35
18240 15-Feb-06 23-Feb-06 8
18240 13-Mar-06 30-Mar-06 17
18468 8-Feb-06 23-Feb-06 15
18468 4-Oct-06 24-Oct-06 20

I€„¢m trying to come up with a way to extract the earliest date
in the
€œdelivered€Â� column and the latest date in the €Å
“verdict€Â� column for
any given
IRB number. If the same number has multiple entries (rows), I then
need
to
know the absolute earliest and latest dates (and the absolute
difference)
over all relevant rows.

From there, I think i can calculate all sorts of useful things, and
make
pretty charts and graphs.

Any ideas?

There can be an unlimited number of rows with the same IRB number,
if
it
matters.

Also, I will eventually be sorting the difference into 15-day
sections,
which I'm currently doing with the following kludgy formula and a
series
of
"countif"s:



=IF(C:C<=15,"<15",(IF(C:C<=30,"<30",(IF(C:C<=45,"< 45",(IF(C:C<=60,"<60","60
")))))))

elegant, it's not; suggestions for this part would be most helpful,
too....

thanks in advance.