How do i get the if statement i nthis formula to work

M

Mark

=IF(((Sheet1!E6="x")*OR(Sheet1!F6="x")*OR(E6="x")*OR(F6="x")),"x",(IF((E6=Sheet1!E6)*AND(F6=Sheet1!F6),3,(IF((E6-F6)=(Sheet1!E6-Sheet1!F6),2,(IF((SIGN(E6-F6))=(SIGN(Sheet1!E6-Sheet1!F6)),1,0)))))))

I want it that if any of the 4 first statements = x that it returns a value
of x. All the fields in Sheet1!E^ and Sheet1!F^ are x. The problem i am
getting is that its returning a value of #VALUE. Not the x i am expecting.
It seems the or statement isnt working the way i expect.
 
B

Bob Phillips

=IF(OR(Sheet1!E6="x",Sheet1!F6="x",E6="x",F6="x"),"x",
IF(AND(E6=Sheet1!E6,F6=Sheet1!F6),3,
IF(E6-F6=Sheet1!E6-Sheet1!F6,2,
IF(SIGN(E6-F6)=SIGN(Sheet1!E6-Sheet1!F6),1,0))))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is a Nested Formula what I need? 1
Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet - Re-Visit 5
counting multiple criteria using SUMPRODUCT and getting #VALUE err 1
blank cells having no value 2
Nested IF or( If And ) can't get it to work 2
Copying Formulas - correctly stated 3
How do I write this formula 1
Calculating sum product between columns. 3

Top