if formula

[duckie]

could someone please help i hope it posts ok
A B C D E F
Rows time
4 0
5 1/11/06 11 1 F5 should say 3 as its more than 0 as C4 is 0
6 7/11/06 10.9 3 F6 should say 3 as it was a better time than C5
7 14/11/06 3 F7 should say 0 as it is left blank
8 21/11/06 2 F8 should say 0 as its left blank
9 28/11/06 11.5 1 F9 should say 1 as it's a less better time in C9
10 5/12/06 10.9 3 F10 should say 2 as its equal the best time

random cells in column C are used with difference times as used in a
running race

at start of season we don't know what cells in column C are to be used

=IF(C5<C4,3,IF(C5=C4,2,IF(C5>C4,1))) I have used this formula in row 5

time is in seconds

 

[David Biddulph]

Firstly, please don't post the same message separately to multiple groups.
Secondly, please don't start a new thread when the question has already been
asked.
Thirdly, you might stand a better chance of getting an answer if you were to
reply to the questions which were asked after your previous post.

 

[Sandy Mann]

I second David's comments.

This array entered formula, (enter it with Ctrl + Shift + Enter not just
Enter), returns what you asked for but I suspect that there will be other
conditions that will come up that are not satisfied. Chnge the C150 to a
row bigger then you will ever use.

=IF(C5="",0,IF(C5=MIN(IF($C$4:C4>0,$C$4:C4)),2,IF(C5=MAX($C$4:C150),1,IF(OR(C5<C4,AND(C4=0,C5>0)),3))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[duckie]

I second David's comments.

This array entered formula, (enter it with Ctrl + Shift + Enter not just
Enter), returns what you asked for but I suspect that there will be other
conditions that will come up that are not satisfied. Chnge the C150 to a
row bigger then you will ever use.

=IF(C5="",0,IF(C5=MIN(IF($C$4:C4>0,$C$4:C4)),2,IF(C5=MAX($C$4:C150),1,IF(OR­(C5<C4,AND(C4=0,C5>0)),3))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message

Firstly, please don't post the same message separately to multiple groups.
Secondly, please don't start a new thread when the question has already
been asked.
Thirdly, you might stand a better chance of getting an answer if you were
to reply to the questions which were asked after your previous post.

- Show quoted text -

Thank you sandy it worked
i am sorry for second post david

 

[Sandy Mann]

Thank you sandy it worked

You're welcome. I find personally that it is best to describe what it is
that you are trying to do as best as you can and then include some example
data & results required.

As I said, the formula works for the data that you supplied but I am still
not sure that it really is what you want. The point about starting a new
thread, especially in a different NG, is that I would have had to search to
find what had been said before. I did not do that, I simply answered the
question that you asked.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[duckie]

You're welcome. I find personally that it is best to describe what it is
that you are trying to do as best as you can and then include some example
data & results required.

As I said, the formula works for the data that you supplied but I am still
not sure that it really is what you want. The point about starting a new
thread, especially in a different NG, is that I would have had to search to
find what had been said before. I did not do that, I simply answered the
question that you asked.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

only 1 small problem with it but thanks for your time
the equal times as in 11.2 seconds in diff cell but same column
comes up as 3 when it should come up as 2

 

[Sandy Mann]

only 1 small problem with it but thanks for your time
the equal times as in 11.2 seconds in diff cell but same column
comes up as 3 when it should come up as 2

But that does not match with your previous requirements. I think that you
are going to have to think your needs through and come up with priorities.
For example:

A B C D E F
Rows time
4 0
5 1/11/06 11 1 F5 should say 3 as its more than 0 as C4 is 0
6 7/11/06 10.9 3 F6 should say 3 as it was a better time than C5
7 14/11/06 3 F7 should say 0 as it is left blank
8 21/11/06 2 F8 should say 0 as its left blank
9 28/11/06 11.5 1 F9 should say 1 as it's a less better time in C9
10 5/12/06 10.9 3 F10 should say 2 as its equal the best time

4 0
5 1/11/06 11 1 F5 should say 3 as its more than 0 as C4 is 0 But:
8 21/11/06 2 F8 should say 0 as its left blank
9 28/11/06 11.5 1 F9 should say 1 as it's a less better time in C9

B4 presumably does not have a time and C4 has 0 in it so should F9 not be 3
as per the instruction for F5?

6 7/11/06 10.9 3 F6 should say 3 as it was a better time than C5

10 5/12/06 10.9 3 F10 should say 2 as its equal the best time

Do you want a time to be given 3 because it is a better time than the cell
above even if it is the best time?

the equal times as in 11.2 seconds in diff cell but same column
comes up as 3 when it should come up as 2

Do you want every matching pair to be given a 2?

See if you can come up with more explicit rules governing what returns you
want. There is a general principle that if you can't write down what you
want then you can't write a formula to do it either.

Try stating what it is that you want to do, what results you want and things
like when you say the best time, if later you have a better time do you want
the previous best time to change and if so to what? etc.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk