Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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 "Karen Smith" wrote in 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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" wrote in 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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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" wrote: 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" wrote in 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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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" wrote: 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" wrote: 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" wrote in 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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
You can do it in a similar way to formula 1.
-- David Biddulph "Karen Smith" wrote in message ... 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" wrote: 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" wrote: 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" wrote in 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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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" wrote: You can do it in a similar way to formula 1. -- David Biddulph "Karen Smith" wrote in message ... 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" wrote: 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" wrote: 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" wrote in 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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF AND Formula Help Needed
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" wrote in message ... 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" wrote: You can do it in a similar way to formula 1. -- David Biddulph "Karen Smith" wrote in message ... 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" wrote: 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" wrote: 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" wrote in 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. "Bernie Deitrick" wrote: 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 "Karen Smith" wrote in 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed | Excel Discussion (Misc queries) | |||
Formula needed | Excel Worksheet Functions | |||
Formula help needed | Excel Worksheet Functions | |||
Formula Set Up Needed | Charts and Charting in Excel | |||
Formula Needed! | Excel Discussion (Misc queries) |