I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a way to
get the oldest date that do not have completed, so is there any way to get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks

Try =MIN(IF(B1:B5<"completed",A1:A5,99999))
you must enter this array formula with Shift+Ctrl+Enter not just Enter
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dreamstar_1961" wrote in message
...
I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a way
to
get the oldest date that do not have completed, so is there any way to get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks

Forgot to add: You will need to format the cell as a date
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dreamstar_1961" wrote in message
...
I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a way
to
get the oldest date that do not have completed, so is there any way to get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks

Try

=min(if(B1:B10<"completed",A1:A10))

Enter the array formula with CTRL SHIFT ENTER

The formula should display with {} around it when you are done.

"Dreamstar_1961" wrote:

I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a way to
get the oldest date that do not have completed, so is there any way to get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks

That gives zero, since cells with 'complete' put zero (false) in the output
array
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Barb Reinhardt" wrote in message
...
Try

=min(if(B1:B10<"completed",A1:A10))

Enter the array formula with CTRL SHIFT ENTER

The formula should display with {} around it when you are done.

"Dreamstar_1961" wrote:

I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a
way to
get the oldest date that do not have completed, so is there any way to
get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks

umnboth these I have tryed, I've now come up with using another cell with
=if(b1:b100="completed","closed",if(a1:a100="","", "open"))
and then {=min(z1:z100="open",a1:a100))} I then set the column of z to
hidden, that solved the problem, it was a long way about but if it works, it
will have to do,

thanks to those that tryed to help with this problem.

"Dreamstar_1961" wrote:

I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a way to
get the oldest date that do not have completed, so is there any way to get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks