Multiple IF/AND/OR statments

  • Thread starter Thread starter Zakynthos
  • Start date Start date
Z

Zakynthos

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
 
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!
 
=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"))))
 
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,"")))))))
 
You're Welcome... But the formula provided by Jacob Sir is looks simple and
Great one...
 
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







- Show quoted text -
Click to expand...

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/com...ions&mid=0d0dd4a7-469a-480e-85fd-f0641be0ea84
 
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 said:
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.
Click to expand...

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 -----
 
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 said:
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.
Click to expand...

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 -----
 
Back
Top