| 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
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 |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#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
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 |
|
#10
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 - |
|
#11
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 |
| 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 | |||
Hybrid Mode
