IF AND Formula Help Needed

K

Karen Smith

I have 2 formulas that I need to add another condition to and everytime I try
it, it doesn't work. Both formulas also need to say: If 'The Worker
Be!'!B8=CD then do nothing with the formulas below.


Formula 1:
=IF(AND($S8="A.",'The Worker Be!'!$G8="New"),$B$2*'The Worker
Be!'!$F8,0)+IF(AND($S8="A.",'The Worker Be!'!$G8="Update"),$B$3*'The Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="New"),$B$2*'The Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="Update"),B3*'The Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker Be!'!$G8="New"),$B$5*'The Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker Be!'!$G8="Update"),$B$5*'The Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker Be!'!$G8="New"),$B$4*'The Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker Be!'!$G8="Update"),$B$4*'The Worker
Be!'!$F8,0)

Formula 2:
=IF(AND(S8="A.",'The Worker Be!'!K8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="B.",'The Worker Be!'!K$8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="C.",'The Worker Be!'!$K8="X"),$D$4*'The Worker
Be!'!$F8,0)+IF(AND(S8="D.",'The Worker Be!'!$K8="X"),$D$3*'The Worker
Be!'!$F8,0)

Any one have any ideas? Thanks in advance for your help.
 
B

Bernie Deitrick

Karen,

There is a inconsistency within your formula 1 that needs to be clarified before we try to simplify
that formula

S8 G8 Returns

A. New B2
A. Update B3
B. New B2
B. Update B3
C. New B5
C. Update B5
D. New B4
D. Update B4

In other words:
(A. OR B.) AND (New) returns B2
(New OR Update) AND (C.) returns B5
(A. OR B.) AND (Update) returns B3
(New OR Update) AND (D.) returns B4

That may actually be what you want - but it isn't a consistent logical structure that could be
simplified as easily if what you really want:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C. OR D.) AND (New) returns B4
(C. OR D.) AND (Update) returns B5

Could G8 ever be something other than New or Update?


HTH,
Bernie
MS Excel MVP
 
K

Karen Smith

I'm sure I've confused things with my logic and formulas, sorry. It is
inconsistant, but its valid for what I'm doing. So, to clarify:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C.) returns B4 regardless of New or Update
(D.) returns B5 regardless of New or Update

Also, G8 can only be New or Update. Thanks for your help.
 
D

David Biddulph

It's always clearer if you try to simplify your original formula and avoid
unnecessary repetition.
I think you wanted (instead of your 9 line formula 1):
='The Worker Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))) ?
[I'm assuming that "C." gives B5 as you said in your formula, not B4 as
you've said in your most recent message.]

To answer your original question, it's easy to add the additional test, but
you haven't said what you mean by "do nothing". If you want it to return a
zero, thern try
=IF('The Worker Be!'!B8="CD",0,'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))))
If you want it to return a blank, just replace the ,0, near the beginning of
the formula with ,"",
 
K

Karen Smith

Thanks so much David, the formula you supplied worked. I'm not that advanced
with formulas yet, so I don't always know how to simplify. Now for question
#2 (which I realized I had wrong in the the 1st note), the formula I need is:

If cell S8 = A. B. or D. than 'WorkerBe!' F8 * D3
If cell S8 = C. than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0

David Biddulph said:
It's always clearer if you try to simplify your original formula and avoid
unnecessary repetition.
I think you wanted (instead of your 9 line formula 1):
='The Worker Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))) ?
[I'm assuming that "C." gives B5 as you said in your formula, not B4 as
you've said in your most recent message.]

To answer your original question, it's easy to add the additional test, but
you haven't said what you mean by "do nothing". If you want it to return a
zero, thern try
=IF('The Worker Be!'!B8="CD",0,'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))))
If you want it to return a blank, just replace the ,0, near the beginning of
the formula with ,"",
--
David Biddulph
Karen Smith said:
I'm sure I've confused things with my logic and formulas, sorry. It is
inconsistant, but its valid for what I'm doing. So, to clarify:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C.) returns B4 regardless of New or Update
(D.) returns B5 regardless of New or Update

Also, G8 can only be New or Update. Thanks for your help.
Click to expand...
Click to expand...
 
K

Karen Smith

Sorry, i forgot to add one more condition, so use this instead:


If cell S8 = A. B. or D. & 'WorkerBe! L8 = x, than 'WorkerBe!' F8 * D3
If cell S8 = C. & 'WorkerBe! L8 = X, than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0
otherwise 0

Karen Smith said:
Thanks so much David, the formula you supplied worked. I'm not that advanced
with formulas yet, so I don't always know how to simplify. Now for question
#2 (which I realized I had wrong in the the 1st note), the formula I need is:

If cell S8 = A. B. or D. than 'WorkerBe!' F8 * D3
If cell S8 = C. than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0

David Biddulph said:
It's always clearer if you try to simplify your original formula and avoid
unnecessary repetition.
I think you wanted (instead of your 9 line formula 1):
='The Worker Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))) ?
[I'm assuming that "C." gives B5 as you said in your formula, not B4 as
you've said in your most recent message.]

To answer your original question, it's easy to add the additional test, but
you haven't said what you mean by "do nothing". If you want it to return a
zero, thern try
=IF('The Worker Be!'!B8="CD",0,'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))))
If you want it to return a blank, just replace the ,0, near the beginning of
the formula with ,"",
--
David Biddulph
Karen Smith said:
I'm sure I've confused things with my logic and formulas, sorry. It is
inconsistant, but its valid for what I'm doing. So, to clarify:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C.) returns B4 regardless of New or Update
(D.) returns B5 regardless of New or Update

Also, G8 can only be New or Update. Thanks for your help.



:

Karen,

There is a inconsistency within your formula 1 that needs to be clarified
before we try to simplify
that formula

S8 G8 Returns

A. New B2
A. Update B3
B. New B2
B. Update B3
C. New B5
C. Update B5
D. New B4
D. Update B4

In other words:
(A. OR B.) AND (New) returns B2
(New OR Update) AND (C.) returns B5
(A. OR B.) AND (Update) returns B3
(New OR Update) AND (D.) returns B4

That may actually be what you want - but it isn't a consistent logical
structure that could be
simplified as easily if what you really want:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C. OR D.) AND (New) returns B4
(C. OR D.) AND (Update) returns B5

Could G8 ever be something other than New or Update?


HTH,
Bernie
MS Excel MVP


I have 2 formulas that I need to add another condition to and everytime
I try
it, it doesn't work. Both formulas also need to say: If 'The Worker
Be!'!B8=CD then do nothing with the formulas below.


Formula 1:
=IF(AND($S8="A.",'The Worker Be!'!$G8="New"),$B$2*'The Worker
Be!'!$F8,0)+IF(AND($S8="A.",'The Worker Be!'!$G8="Update"),$B$3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="New"),$B$2*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="Update"),B3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker Be!'!$G8="New"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker Be!'!$G8="Update"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker Be!'!$G8="New"),$B$4*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker Be!'!$G8="Update"),$B$4*'The
Worker
Be!'!$F8,0)

Formula 2:
=IF(AND(S8="A.",'The Worker Be!'!K8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="B.",'The Worker Be!'!K$8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="C.",'The Worker Be!'!$K8="X"),$D$4*'The Worker
Be!'!$F8,0)+IF(AND(S8="D.",'The Worker Be!'!$K8="X"),$D$3*'The Worker
Be!'!$F8,0)

Any one have any ideas? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
D

David Biddulph

You can do it in a similar way to formula 1.
--
David Biddulph

Karen Smith said:
Sorry, i forgot to add one more condition, so use this instead:


If cell S8 = A. B. or D. & 'WorkerBe! L8 = x, than 'WorkerBe!' F8 * D3
If cell S8 = C. & 'WorkerBe! L8 = X, than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0
otherwise 0

Karen Smith said:
Thanks so much David, the formula you supplied worked. I'm not that
advanced
with formulas yet, so I don't always know how to simplify. Now for
question
#2 (which I realized I had wrong in the the 1st note), the formula I need
is:

If cell S8 = A. B. or D. than 'WorkerBe!' F8 * D3
If cell S8 = C. than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0

David Biddulph said:
It's always clearer if you try to simplify your original formula and
avoid
unnecessary repetition.
I think you wanted (instead of your 9 line formula 1):
='The Worker Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))) ?
[I'm assuming that "C." gives B5 as you said in your formula, not B4 as
you've said in your most recent message.]

To answer your original question, it's easy to add the additional test,
but
you haven't said what you mean by "do nothing". If you want it to
return a
zero, thern try
=IF('The Worker Be!'!B8="CD",0,'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))))
If you want it to return a blank, just replace the ,0, near the
beginning of
the formula with ,"",
--
David Biddulph
I'm sure I've confused things with my logic and formulas, sorry. It
is
inconsistant, but its valid for what I'm doing. So, to clarify:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C.) returns B4 regardless of New or Update
(D.) returns B5 regardless of New or Update

Also, G8 can only be New or Update. Thanks for your help.



:

Karen,

There is a inconsistency within your formula 1 that needs to be
clarified
before we try to simplify
that formula

S8 G8 Returns

A. New B2
A. Update B3
B. New B2
B. Update B3
C. New B5
C. Update B5
D. New B4
D. Update B4

In other words:
(A. OR B.) AND (New) returns B2
(New OR Update) AND (C.) returns B5
(A. OR B.) AND (Update) returns B3
(New OR Update) AND (D.) returns B4

That may actually be what you want - but it isn't a consistent
logical
structure that could be
simplified as easily if what you really want:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C. OR D.) AND (New) returns B4
(C. OR D.) AND (Update) returns B5

Could G8 ever be something other than New or Update?


HTH,
Bernie
MS Excel MVP


message
I have 2 formulas that I need to add another condition to and
everytime
I try
it, it doesn't work. Both formulas also need to say: If 'The
Worker
Be!'!B8=CD then do nothing with the formulas below.


Formula 1:
=IF(AND($S8="A.",'The Worker Be!'!$G8="New"),$B$2*'The Worker
Be!'!$F8,0)+IF(AND($S8="A.",'The Worker
Be!'!$G8="Update"),$B$3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="New"),$B$2*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="Update"),B3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker Be!'!$G8="New"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker
Be!'!$G8="Update"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker Be!'!$G8="New"),$B$4*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker
Be!'!$G8="Update"),$B$4*'The
Worker
Be!'!$F8,0)

Formula 2:
=IF(AND(S8="A.",'The Worker Be!'!K8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="B.",'The Worker Be!'!K$8="X"),$D$3*'The
Worker
Be!'!$F8,0)+IF(AND(S8="C.",'The Worker Be!'!$K8="X"),$D$4*'The
Worker
Be!'!$F8,0)+IF(AND(S8="D.",'The Worker Be!'!$K8="X"),$D$3*'The
Worker
Be!'!$F8,0)

Any one have any ideas? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
K

Karen Smith

Ok, I did that, but now my formula is giving me a "false" and it has to do
with the 2nd part I added =IF('The Worker Bee!'$H8-"NO",0.....basically, the
formula needs to check with B8=CD put a 0, then if H8=Yes then do the rest of
the formula, but if it = no, then 0. I'm sure it's an easy fix, but for the
life of me I can't figure it out. Can you help?

=IF('The Worker Be!'!$B8="CD",0=IF('The Worker Be!'!$H8="NO",0,IF('The
Worker Be!'!$L8="X",'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B",$S8="D."),D$3,IF($S8="C.",D$4,0)))))

David Biddulph said:
You can do it in a similar way to formula 1.
--
David Biddulph

Karen Smith said:
Sorry, i forgot to add one more condition, so use this instead:


If cell S8 = A. B. or D. & 'WorkerBe! L8 = x, than 'WorkerBe!' F8 * D3
If cell S8 = C. & 'WorkerBe! L8 = X, than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0
otherwise 0

Karen Smith said:
Thanks so much David, the formula you supplied worked. I'm not that
advanced
with formulas yet, so I don't always know how to simplify. Now for
question
#2 (which I realized I had wrong in the the 1st note), the formula I need
is:

If cell S8 = A. B. or D. than 'WorkerBe!' F8 * D3
If cell S8 = C. than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0

:

It's always clearer if you try to simplify your original formula and
avoid
unnecessary repetition.
I think you wanted (instead of your 9 line formula 1):
='The Worker Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))) ?
[I'm assuming that "C." gives B5 as you said in your formula, not B4 as
you've said in your most recent message.]

To answer your original question, it's easy to add the additional test,
but
you haven't said what you mean by "do nothing". If you want it to
return a
zero, thern try
=IF('The Worker Be!'!B8="CD",0,'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))))
If you want it to return a blank, just replace the ,0, near the
beginning of
the formula with ,"",
--
David Biddulph
I'm sure I've confused things with my logic and formulas, sorry. It
is
inconsistant, but its valid for what I'm doing. So, to clarify:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C.) returns B4 regardless of New or Update
(D.) returns B5 regardless of New or Update

Also, G8 can only be New or Update. Thanks for your help.



:

Karen,

There is a inconsistency within your formula 1 that needs to be
clarified
before we try to simplify
that formula

S8 G8 Returns

A. New B2
A. Update B3
B. New B2
B. Update B3
C. New B5
C. Update B5
D. New B4
D. Update B4

In other words:
(A. OR B.) AND (New) returns B2
(New OR Update) AND (C.) returns B5
(A. OR B.) AND (Update) returns B3
(New OR Update) AND (D.) returns B4

That may actually be what you want - but it isn't a consistent
logical
structure that could be
simplified as easily if what you really want:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C. OR D.) AND (New) returns B4
(C. OR D.) AND (Update) returns B5

Could G8 ever be something other than New or Update?


HTH,
Bernie
MS Excel MVP


message
I have 2 formulas that I need to add another condition to and
everytime
I try
it, it doesn't work. Both formulas also need to say: If 'The
Worker
Be!'!B8=CD then do nothing with the formulas below.


Formula 1:
=IF(AND($S8="A.",'The Worker Be!'!$G8="New"),$B$2*'The Worker
Be!'!$F8,0)+IF(AND($S8="A.",'The Worker
Be!'!$G8="Update"),$B$3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="New"),$B$2*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker Be!'!$G8="Update"),B3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker Be!'!$G8="New"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker
Be!'!$G8="Update"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker Be!'!$G8="New"),$B$4*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker
Be!'!$G8="Update"),$B$4*'The
Worker
Be!'!$F8,0)

Formula 2:
=IF(AND(S8="A.",'The Worker Be!'!K8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="B.",'The Worker Be!'!K$8="X"),$D$3*'The
Worker
Be!'!$F8,0)+IF(AND(S8="C.",'The Worker Be!'!$K8="X"),$D$4*'The
Worker
Be!'!$F8,0)+IF(AND(S8="D.",'The Worker Be!'!$K8="X"),$D$3*'The
Worker
Be!'!$F8,0)

Any one have any ideas? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
D

David Biddulph

Karen,

I'll give you the same advice that I usually give to folk who are struggling
to understand a long formula, that is to break it down into manageable
chunks and see what each part is doing.

The usual reason for getting a FALSE result from an IF statement is that you
have forgotten to specify one of the outcomes. Remind yourself of the
syntax of an IF statement:
IF(logical_test,value_if_true,value_if_false)
If you don't specify the value_if_false (i.e. the part after the second
comma) then it will default to FALSE. Look at each of your IF statements
and see whether you've specified the 3 parameters as shown above. [You can,
of course, nest the functions so that value_if_true or value_if_false is
itself another function, often another IF function.]

If you look at your formula you've got a sensible syntax if you break it
down all the way to the stage of
='The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B",$S8="D."),D$3,IF($S8="C.",D$4,0))
but if you go one stage further out you have
=IF('The Worker Be!'!$L8="X",'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B",$S8="D."),D$3,IF($S8="C.",D$4,0)))
and there the outer IF statement doesn't have a value_if_false, so it will
default to FALSE as a result if L8<>"X".

Going out further, your IF statement for H8="NO" does have its 2 outcomes
specified, but you've used that in a logical test 0=IF(... [which will
return TRUE or FALSE] which you've called in the case where B8="CD", and you
haven't specified an alternative outcome in that case either. I guess that
you intended 0,IF(... rather than 0=IF(... ?

As I said earlier, just look at your formula a stage at a time, and it's
easy.
--
David Biddulph

Karen Smith said:
Ok, I did that, but now my formula is giving me a "false" and it has to do
with the 2nd part I added =IF('The Worker Bee!'$H8-"NO",0.....basically,
the
formula needs to check with B8=CD put a 0, then if H8=Yes then do the rest
of
the formula, but if it = no, then 0. I'm sure it's an easy fix, but for
the
life of me I can't figure it out. Can you help?

=IF('The Worker Be!'!$B8="CD",0=IF('The Worker Be!'!$H8="NO",0,IF('The
Worker Be!'!$L8="X",'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B",$S8="D."),D$3,IF($S8="C.",D$4,0)))))

David Biddulph said:
You can do it in a similar way to formula 1.
--
David Biddulph

Karen Smith said:
Sorry, i forgot to add one more condition, so use this instead:


If cell S8 = A. B. or D. & 'WorkerBe! L8 = x, than 'WorkerBe!' F8 * D3
If cell S8 = C. & 'WorkerBe! L8 = X, than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0
otherwise 0

:

Thanks so much David, the formula you supplied worked. I'm not that
advanced
with formulas yet, so I don't always know how to simplify. Now for
question
#2 (which I realized I had wrong in the the 1st note), the formula I
need
is:

If cell S8 = A. B. or D. than 'WorkerBe!' F8 * D3
If cell S8 = C. than 'WorkerBe!' F8 & D4
In both cases, IF('The Worker Be!'!B8="CD",0

:

It's always clearer if you try to simplify your original formula and
avoid
unnecessary repetition.
I think you wanted (instead of your 9 line formula 1):
='The Worker Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))) ?
[I'm assuming that "C." gives B5 as you said in your formula, not B4
as
you've said in your most recent message.]

To answer your original question, it's easy to add the additional
test,
but
you haven't said what you mean by "do nothing". If you want it to
return a
zero, thern try
=IF('The Worker Be!'!B8="CD",0,'The Worker
Be!'!$F8*IF(OR($S8="A.",$S8="B."),IF('The Worker
Be!'!$G8="New",$B$2,$B$3),IF($S8="C.",$B$5,IF($S8="D.",$B$4,0))))
If you want it to return a blank, just replace the ,0, near the
beginning of
the formula with ,"",
--
David Biddulph
message
I'm sure I've confused things with my logic and formulas, sorry.
It
is
inconsistant, but its valid for what I'm doing. So, to clarify:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C.) returns B4 regardless of New or Update
(D.) returns B5 regardless of New or Update

Also, G8 can only be New or Update. Thanks for your help.



:

Karen,

There is a inconsistency within your formula 1 that needs to be
clarified
before we try to simplify
that formula

S8 G8 Returns

A. New B2
A. Update B3
B. New B2
B. Update B3
C. New B5
C. Update B5
D. New B4
D. Update B4

In other words:
(A. OR B.) AND (New) returns B2
(New OR Update) AND (C.) returns B5
(A. OR B.) AND (Update) returns B3
(New OR Update) AND (D.) returns B4

That may actually be what you want - but it isn't a consistent
logical
structure that could be
simplified as easily if what you really want:

(A. OR B.) AND (New) returns B2
(A. OR B.) AND (Update) returns B3
(C. OR D.) AND (New) returns B4
(C. OR D.) AND (Update) returns B5

Could G8 ever be something other than New or Update?


HTH,
Bernie
MS Excel MVP


message
I have 2 formulas that I need to add another condition to and
everytime
I try
it, it doesn't work. Both formulas also need to say: If 'The
Worker
Be!'!B8=CD then do nothing with the formulas below.


Formula 1:
=IF(AND($S8="A.",'The Worker Be!'!$G8="New"),$B$2*'The Worker
Be!'!$F8,0)+IF(AND($S8="A.",'The Worker
Be!'!$G8="Update"),$B$3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker
Be!'!$G8="New"),$B$2*'The
Worker
Be!'!$F8,0)+IF(AND($S8="B.",'The Worker
Be!'!$G8="Update"),B3*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker
Be!'!$G8="New"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="C.",'The Worker
Be!'!$G8="Update"),$B$5*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker
Be!'!$G8="New"),$B$4*'The
Worker
Be!'!$F8,0)+IF(AND($S8="D.",'The Worker
Be!'!$G8="Update"),$B$4*'The
Worker
Be!'!$F8,0)

Formula 2:
=IF(AND(S8="A.",'The Worker Be!'!K8="X"),$D$3*'The Worker
Be!'!$F8,0)+IF(AND(S8="B.",'The Worker Be!'!K$8="X"),$D$3*'The
Worker
Be!'!$F8,0)+IF(AND(S8="C.",'The Worker Be!'!$K8="X"),$D$4*'The
Worker
Be!'!$F8,0)+IF(AND(S8="D.",'The Worker Be!'!$K8="X"),$D$3*'The
Worker
Be!'!$F8,0)

Any one have any ideas? Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 

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

IF AND FORMULA 1
Formula Help 3
Conditional Formatting Using Min/Max 10
IF AND Formula Help Needed 7
IF Formula Help 4
IF AND OR Formula Help 1
Averageing monthly data by type 2
Creating automated timesheets 2

Top