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

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

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

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

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

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 -

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

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

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

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

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

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

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