IF AND Formula Help Needed

[Karen Smith]

Can someone help me create a formula for:

If tab "Worker Be" cell B9 = "CD" and
"Worker Be" cell J9 is empty and
Y9 (on current tab) does not = "C."
then the answer should be zero

then
If tab "Worker Be" B9 = "SM" and
"Worker Be" H9 = "Yes" and
if "Worker Be" J9 = either "Big Word" or "WTOO" or "Global Doc"
than ("Worker Be" E9 * $22.50 plus "Worker Be" AF9 * $200) * "Worker Be" I9
otherwise the answer should be zero

 

[Don Guillett]

Look in the help index for AND & OR & IF. Study the components and then see
how to put together with IF.

 

[Karen Smith]

I've already done that and have worked on the formula for 6 hours, I can't
get it to work. That's why I asked for help creating it.

 

[Don Guillett]

1st scenario
=IF(AND(os!B9="cd",LEN(TRIM(os!J9))<1,y9<>"c"),0,nextif)
Can you now work it out?

 

[Pete_UK]

Okay, try this:

=IF(AND('Worker Be'!B9="CD",'Worker Be'!J9="",Y9<>"C"),
0,IF(AND('Worker Be'!B9="SM",'Worker Be'!H9="Yes",OR('Worker Be'!
J9="Big Word",'Worker Be'!J9="WTOO",'Worker Be'!J9="Global Doc")),
('Worker Be'!E9*22.5+'Worker Be'!AF9*200)*'Worker Be'!I9,0))

Hope this helps.

Pete

 

[David Biddulph]

The syntax of the IF, AND, and OR functions are given, with examples, in
Excel help.

If you look back to a question from Karen Smith a couple of months ago,
advice was given as to how to analyse a long formula. The advice remains
unchanged, and applies equally to synthesis as to analysis; look at it a
section at a time.

 

[KC]

Pete, thanks so much. You just did in 2 minutes, what I couldn't do in 6
hours. I really appreciate your help.

 

[Pete_UK]

Hi Karen,

glad it worked for you - I just transcribed what you had given, so if
you study the solution against your problem definition then perhaps
you will be able to see how to save yourself hours in future if a
similar problem arises for you.

Pete