Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if formula
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(C5C4,1))) I have used this formula in row 5 time is in seconds |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if formula
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. -- David Biddulph "duckie" wrote in message ups.com... 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(C5C4,1))) I have used this formula in row 5 time is in seconds |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if formula
On Oct 2, 7:14 am, "Sandy Mann" wrote:
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:C40,$C$4:C4)),2,IF( C5=MAX($C$4:C150),1,IF(OR*(C5<C4,AND(C4=0,C50)),3 )))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "David Biddulph" <groups [at] biddulph.org.uk wrote in m... 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. -- David Biddulph "duckie" wrote in message oups.com... 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(C5C4,1))) I have used this formula in row 5 time is in seconds- Hide quoted text - - Show quoted text - Thank you sandy it worked i am sorry for second post david |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if formula
"duckie" wrote in message
oups.com... 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 Replace @mailinator.com with @tiscali.co.uk |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if formula
On Oct 2, 6:44 pm, "Sandy Mann" wrote:
"duckie" wrote in message oups.com... 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 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if formula
"duckie" wrote in message
ups.com... 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 Replace @mailinator.com with @tiscali.co.uk "duckie" wrote in message ups.com... On Oct 2, 6:44 pm, "Sandy Mann" wrote: "duckie" wrote in message oups.com... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|