| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF/AND/OR statments
What would be the format of the IF/AND/OR nested statement to return the
following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
Use this formula in T1 Cell...
=IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5,IF(AND((R1=5),(S1=25)),5 ,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20) ),4,IF(AND((R1=4),(S1=20)),5,""))))))) Now copy the T1 cell formula and apply it for the remaining cells. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
Try the below
=FLOOR(S1/R1,1) If this post helps click Yes --------------- Jacob Skaria "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
=IF(AND(R1=9,S1=36),4,IF(AND(R1=7,S1=36),5,IF(AND( R1=6,S1=30),5,IF(AND(R1=5,S1=25),5,IF(AND(R1=6.25, S1=25),4,IF(AND(R1=5,S1=20),4,IF(AND(R1=4,S1=20),5 ,"answer
undefined"))))))) or =IF(S1=36,IF(R1=9,4,IF(R1=7,5,"undefined")),IF(S1= 30,IF(R1=6,5,"undefined"),IF(S1=25,IF(R1=5,5,IF(R1 =6.25,4,"undefined")),IF(S1=20,IF(R1=5,4,IF(R1=4,5 ,"undefined")),"undefined")))) -- David Biddulph "Zakynthos" wrote in message ... What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
It might be clearer if you throw away a heap of unnecessary parentheses.
You can simplify it from =IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5,IF(AND((R1=5),(S1=25)),5 ,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20) ),4,IF(AND((R1=4),(S1=20)),5,""))))))) to =IF(AND(R1=9,S1=36),4,IF(AND(R1=7,S1=36),5,IF(AND( R1=6,S1=30),5,IF(AND(R1=5,S1=25),5,IF(AND(R1=6.25, S1=25),4,IF(AND(R1=5,S1=20),4,IF(AND(R1=4,S1=20),5 ,""))))))) -- David Biddulph "Ms-Exl-Learner" wrote in message ... Use this formula in T1 Cell... =IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5,IF(AND((R1=5),(S1=25)),5 ,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20) ),4,IF(AND((R1=4),(S1=20)),5,""))))))) Now copy the T1 cell formula and apply it for the remaining cells. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
excellent!
On 10 Lis, 13:44, Jacob Skaria wrote: Try the below =FLOOR(S1/R1,1) If this post helps click Yes --------------- Jacob Skaria "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
That's great, worked perfectly - thank you very much - you've saved me a
headache!!! "Ms-Exl-Learner" wrote: Use this formula in T1 Cell... =IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5,IF(AND((R1=5),(S1=25)),5 ,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20) ),4,IF(AND((R1=4),(S1=20)),5,""))))))) Now copy the T1 cell formula and apply it for the remaining cells. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
You're Welcome... But the formula provided by Jacob Sir is looks simple and
Great one... -------------------- (Ms-Exl-Learner) -------------------- "Zakynthos" wrote: That's great, worked perfectly - thank you very much - you've saved me a headache!!! "Ms-Exl-Learner" wrote: Use this formula in T1 Cell... =IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5,IF(AND((R1=5),(S1=25)),5 ,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20) ),4,IF(AND((R1=4),(S1=20)),5,""))))))) Now copy the T1 cell formula and apply it for the remaining cells. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
On Nov 10, 5:55*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: It might be clearer if you throw away a heap of unnecessary parentheses. You can simplify it from =IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5*,IF(AND((R1=5),(S1=25)), 5,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20 )*),4,IF(AND((R1=4),(S1=20)),5,""))))))) to =IF(AND(R1=9,S1=36),4,IF(AND(R1=7,S1=36),5,IF(AND( R1=6,S1=30),5,IF(AND(R1=5*,S1=25),5,IF(AND(R1=6.25 ,S1=25),4,IF(AND(R1=5,S1=20),4,IF(AND(R1=4,S1=20), 5*,""))))))) -- David Biddulph "Ms-Exl-Learner" wrote in message ... Use this formula in T1 Cell... =IF(AND((R1=9),(S1=36)),4,IF(AND((R1=7),(S1=36)),5 ,IF(AND((R1=6),(S1=30)),5*,IF(AND((R1=5),(S1=25)), 5,IF(AND((R1=6.25),(S1=25)),4,IF(AND((R1=5),(S1=20 )*),4,IF(AND((R1=4),(S1=20)),5,""))))))) Now copy the T1 cell formula and apply it for the remaining cells. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Zakynthos" wrote: What would be the format of the IF/AND/OR nested statement to return the following: If R1 contains a 9 and S1 contains a 36 I want a 4 in T1 If R1 contains a 7 and S1 contains a 36 I want a 5 in T1 If R1 contains a 6 and S1 contains a 30 I want a 5 in T1 If R1 contains a 5 and S1 contains a 25 I want a 5 in T1 If R1 contains a 6.25 and S1 contains a 25 I want a 4 in T1 If R1 contains a 5 and S1 contains a 20 I want a 4 in T1 If R1 contains a 4 and S1 contains a 20 I want a 5 in T1 Many thanks for your help- Hide quoted text - - Show quoted text - David Sir, Thanks for your guidance and in future I will avoid the unnecessary paranthesis in my foruma. Can you please tell me why some of the posts posted using Google Newsgroups cannot be viewed in the below link. Because I cant able to see your reply in the below link... http://www.microsoft.com/office/comm...d-f0641be0ea84 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
The reason is that Microsoft's web interface to the newsgroup is notoriously
unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. That's why many of us don't use it. -- David Biddulph "Ms-Xl-Learner" wrote in message ... .... Can you please tell me why some of the posts posted using Google Newsgroups cannot be viewed in the below link. Because I cant able to see your reply in the below link... http://www.microsoft.com/office/comm...d-f0641be0ea84 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
"David Biddulph" <groups [at] biddulph.org.uk wrote:
The reason is that Microsoft's web interface to the newsgroup is notoriously unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. I think the current problem is much more pervasive than the usual "notorious unreliability" of the MS Discussion Group interface and server, if my experiments with m.p.test.here are any indication. Postings originating on the Google Groups, msnews.microsoft.com and MS Discussion Groups servers all propagated properly to the GG and MSNews servers. But it seems that only postings originating on the MSDG server are visible on the MSDG server. Although this has been an intermittent problem for many years, recently it seems to be a consistent failure. And this is the first time I've seen the problem with the MSDG server affecting even MSNews postings. It is difficult to say just how "recent", because of the limited (almost non-existent) search capability on the MSDG server. But I would say the persistent problem started between 9:11 AM and 11:43 PM PST on Nov 2. I found one thread in m.p.e.programming where I posted responses using the MSNews server on Nov 2 at 9:11 AM and Nov 3 at 1:06 AM PST. The first response appears on the MSDG server, but not the second response. But in the same thread, there is a response from someone using GG on Nov 2 at 11:43 PM. That also does not apprear on the MSDG server. Bottom line: For now, if you are responding to someone who posted using the MSDG server, you need to use the MSDG server yourself if you want them to see your response :-(. Hopefully, MS will fix this persistent problem someday -- and we return to the intermittent misbehavior :-(. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The reason is that Microsoft's web interface to the newsgroup is notoriously unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. That's why many of us don't use it. -- David Biddulph "Ms-Xl-Learner" wrote in message ... ... Can you please tell me why some of the posts posted using Google Newsgroups cannot be viewed in the below link. Because I cant able to see your reply in the below link... http://www.microsoft.com/office/comm...d-f0641be0ea84 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
Thank you for that clear explanation of the problem, Joe. The other way of
putting what you said is that if anyone wants to be confident of seeing the replies to their question they should *not* use the broken MSDG server. -- David Biddulph "Joe User" <joeu2004 wrote in message ... "David Biddulph" <groups [at] biddulph.org.uk wrote: The reason is that Microsoft's web interface to the newsgroup is notoriously unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. I think the current problem is much more pervasive than the usual "notorious unreliability" of the MS Discussion Group interface and server, if my experiments with m.p.test.here are any indication. Postings originating on the Google Groups, msnews.microsoft.com and MS Discussion Groups servers all propagated properly to the GG and MSNews servers. But it seems that only postings originating on the MSDG server are visible on the MSDG server. Although this has been an intermittent problem for many years, recently it seems to be a consistent failure. And this is the first time I've seen the problem with the MSDG server affecting even MSNews postings. It is difficult to say just how "recent", because of the limited (almost non-existent) search capability on the MSDG server. But I would say the persistent problem started between 9:11 AM and 11:43 PM PST on Nov 2. I found one thread in m.p.e.programming where I posted responses using the MSNews server on Nov 2 at 9:11 AM and Nov 3 at 1:06 AM PST. The first response appears on the MSDG server, but not the second response. But in the same thread, there is a response from someone using GG on Nov 2 at 11:43 PM. That also does not apprear on the MSDG server. Bottom line: For now, if you are responding to someone who posted using the MSDG server, you need to use the MSDG server yourself if you want them to see your response :-(. Hopefully, MS will fix this persistent problem someday -- and we return to the intermittent misbehavior :-(. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The reason is that Microsoft's web interface to the newsgroup is notoriously unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. That's why many of us don't use it. -- David Biddulph "Ms-Xl-Learner" wrote in message ... ... Can you please tell me why some of the posts posted using Google Newsgroups cannot be viewed in the below link. Because I cant able to see your reply in the below link... http://www.microsoft.com/office/comm...d-f0641be0ea84 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF/AND/OR statments
"David Biddulph" <groups [at] biddulph.org.uk wrote:
The other way of putting what you said is that if anyone wants to be confident of seeing the replies to their question they should *not* use the broken MSDG server. Arguably, yes. But how do you tell that to someone who is already using (only) the MSDG web interface ;-)? (Hint: How will they see your posting?) The point is: we each can control our own behavior, but we cannot control the behavior of others. Generally, I'm content to "ignore" (i.e. not worry about) the MSDG user; whatever happens happens (or not). That was especially true when the problem of "lost" external responses was intermittent. But for now, if I really want an MSDG user to see my response (rarely), I will use the MSDG interface to post it -- if I remember ;-). Hopefully this is a short-term situation. But it seems to have persisted for more than a week so far. I can (almost) understand MS shutting out Google Groups postings, they being a major source of spam (albeit not the only source). But I suspect it is unintentional now because they are also shutting out postings from their own MSNews server. Hmm, perhaps because the MSNews server is failing to filter out GG spam? I have not noticed that much in the m.p.excel NGs. But perhaps some other m.public NGs are getting hit hard. Oh well, I digress.... "The horse is dead" ;-). ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Thank you for that clear explanation of the problem, Joe. The other way of putting what you said is that if anyone wants to be confident of seeing the replies to their question they should *not* use the broken MSDG server. -- David Biddulph "Joe User" <joeu2004 wrote in message ... "David Biddulph" <groups [at] biddulph.org.uk wrote: The reason is that Microsoft's web interface to the newsgroup is notoriously unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. I think the current problem is much more pervasive than the usual "notorious unreliability" of the MS Discussion Group interface and server, if my experiments with m.p.test.here are any indication. Postings originating on the Google Groups, msnews.microsoft.com and MS Discussion Groups servers all propagated properly to the GG and MSNews servers. But it seems that only postings originating on the MSDG server are visible on the MSDG server. Although this has been an intermittent problem for many years, recently it seems to be a consistent failure. And this is the first time I've seen the problem with the MSDG server affecting even MSNews postings. It is difficult to say just how "recent", because of the limited (almost non-existent) search capability on the MSDG server. But I would say the persistent problem started between 9:11 AM and 11:43 PM PST on Nov 2. I found one thread in m.p.e.programming where I posted responses using the MSNews server on Nov 2 at 9:11 AM and Nov 3 at 1:06 AM PST. The first response appears on the MSDG server, but not the second response. But in the same thread, there is a response from someone using GG on Nov 2 at 11:43 PM. That also does not apprear on the MSDG server. Bottom line: For now, if you are responding to someone who posted using the MSDG server, you need to use the MSDG server yourself if you want them to see your response :-(. Hopefully, MS will fix this persistent problem someday -- and we return to the intermittent misbehavior :-(. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... The reason is that Microsoft's web interface to the newsgroup is notoriously unreliable. As you have seen, messages which reach usenet news servers and Google's archive sometimes fail to be displayed on Microsoft's web interface. That's why many of us don't use it. -- David Biddulph "Ms-Xl-Learner" wrote in message ... ... Can you please tell me why some of the posts posted using Google Newsgroups cannot be viewed in the below link. Because I cant able to see your reply in the below link... http://www.microsoft.com/office/comm...d-f0641be0ea84 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If then Statments | Excel Worksheet Functions | |||
| If Statments | Excel Discussion (Misc queries) | |||
| AND OR IF Statments | Excel Worksheet Functions | |||
| Multiple Vlookups & IF statments | Excel Worksheet Functions | |||
| IF statments for multiple listings. | Excel Worksheet Functions | |||
Linear Mode
