If/And function help

  • Thread starter Thread starter Milehigh
  • Start date Start date
M

Milehigh

Hi,

I am trying to compare two different cells - where one
cell displays - CR, PR, SD, PD and the other cell
displays CR, SD, PD.

What I need to do is have the 3rd cell - where I want to
display a result based on the combination of the 2
previous cells:

If Cell 1 or Cell 2 = PD then Cell 3 = PD
If Cell 1 = SD and Cell 2 does not equal PD then Cell 3 =
SD
If Cell 1 = PR and Cell 2 does not equal PD then Cell 3 =
PR
If Cell 1 = CR and Cell 2 = SD then Cell 3 = PR
If Cell 1 = CR and Cell 2 = CR then Cell 3 = CR

I have tried the following formula in Cell 3 (with Cell 1
being d21 and Cell 2 = d22):

=if (d21="pd","pd",if (d22="pd","pd")),and(d21="SD",
d22<>"PD", "SD"),and(d21="PR",d22<>"PD", "PR"),and
(d21="CR",d22="SD", "PR"), and(d21="CR", d22="CR", "CR")

Any help would be greatly appreciated. I think I am on
the right track but not sure. Thanks in advance.
 
Without any pretext...

=IF((OR(A1="PD",B1="PD")),"PD",(IF(AND(A1="SD",B1<>"PD"),"SD",IF(AND(A1="PR",B1<>"PD"),"PR",(IF(AND(A1="CR",B1="SD"),"PR",IF(AND(A1="CR",B1="CR"),"CR","")))))))

Hope that helps. It seems to work when I did it.

*wipes forehead*

-Bo
 
I just realized, I forgot to change all my A1 / B1 things.

If it says A1, change it to your first cell, if it says B1, change it
to your second cell. It should work just fine with the changes.

-Bob
 
Find Replace is my friend. *duhr*

=IF((OR(D21="PD",D22="PD")),"PD",(IF(AND(D21="SD",D22<>"PD"),"SD",IF(AND(D21="PR",D22<>"PD"),"PR",(IF(AND(D21="CR",D22="SD"),"PR",IF(AND(D21="CR",D22="CR"),"CR","")))))))
 
That's it! Thanks for your help - I have been pulling my
hairout trying to figure it out for the last hour!
Thanks again.....
 
Slightly simplified to

=IF((OR(D21="PD",D22="PD")),"PD",(IF(AND(D21="SD",D22<>"PD"),"SD",IF(OR(AND(
D21="PR",D22<>"PD"),AND(D21="CR",D22="SD")),"PR",IF(AND(D21="CR",D22="CR"),"
CR","")))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

beeawwb > said:
Find Replace is my friend. *duhr*
Click to expand...
=IF((OR(D21="PD",D22="PD")),"PD",(IF(AND(D21="SD",D22<>"PD"),"SD",IF(AND(D21
 
Bob Phillips said:
Slightly simplified to

=IF((OR(D21="PD",D22="PD")),"PD",(IF(AND(D21="SD",D22<>"PD"),"SD",IF(OR(AND (
"
CR","")))))
Click to expand...
....

It could be simplified further.

=IF(D22="PD","PD",IF(OR(D21={"PD","SD","PR"}),D21,
IF(AND(D21="CR",D22="SD"),"PR",IF(AND(D21="CR",D22="CR"),"CR",""))))
 
Back
Top