If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Formula is sending me over the edge
This formula works when 47780 is input into D8, however,
it does not work if C,P or 53S is in D9: =IF(OR(AND('Appendix B'!D8="(47780)",J19K190),AND(OR ('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'! D9="53S"),J19K190)),0,+J19K19) This OTHER formula works when P (just an examplesame formula is used for C and 53S. =IF(ISNUMBER(FIND("P",'Appendix B'!D9)),'Appendix B'!D42,0) I need to incorporte the IF(ISNUMBER(FIND("P",'Appendix B'! D9... formula into the existing 1st formula for C,P, 53S and can't do it. Please help if you can. Thanks in advance. 
Ads 
#2




Formula is sending me over the edge
If 47780 is in D8 and C, P, or 53S is in D9, what do you expect the result
to be? Your current formula evaluates as TRUE if D8 contains 47780 and/or D9 contains C, P, or 53S. For any of these combinations, the result, in accordance with your formula, will be 0. If D8 does not contain 47780 and D9 does not contain C, P, or 53S then the formula evaluates as FALSE and J19K19 will be returned. Are you trying to come up with something else? Also, how do you want to incorporate the ISNUMBER formula into your original formula? What is the purpose? Post back and maybe we can find the right answer.  Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me."  AA Milne, Winnie the Pooh wrote in message ... This formula works when 47780 is input into D8, however, it does not work if C,P or 53S is in D9: =IF(OR(AND('Appendix B'!D8="(47780)",J19K190),AND(OR ('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'! D9="53S"),J19K190)),0,+J19K19) This OTHER formula works when P (just an examplesame formula is used for C and 53S. =IF(ISNUMBER(FIND("P",'Appendix B'!D9)),'Appendix B'!D42,0) I need to incorporte the IF(ISNUMBER(FIND("P",'Appendix B'! D9... formula into the existing 1st formula for C,P, 53S and can't do it. Please help if you can. Thanks in advance. 
#3




Formula is sending me over the edge
Michael
Thanks for your response What I'm trying to accomplish is that if D8=47780 OR D9=C or P or 53S AND if J19K19 is greater than 0, then return 0 (true) otherwise return +J19 K19)(false), does this help? The problem is that in D9 the C,P could have something preceeding it (i.e., 26P), and the 53 could have something after it rather than the S, that is why without ISNUMBER, it doesn't recognize the C or P or 53S? Thank you for helping. Original Message If 47780 is in D8 and C, P, or 53S is in D9, what do you expect the result to be? Your current formula evaluates as TRUE if D8 contains 47780 and/or D9 contains C, P, or 53S. For any of these combinations, the result, in accordance with your formula, will be 0. If D8 does not contain 47780 and D9 does not contain C, P, or 53S then the formula evaluates as FALSE and J19K19 will be returned. Are you trying to come up with something else? Also, how do you want to incorporate the ISNUMBER formula into your original formula? What is the purpose? Post back and maybe we can find the right answer.  Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me."  AA Milne, Winnie the Pooh wrote in message ... This formula works when 47780 is input into D8, however, it does not work if C,P or 53S is in D9: =IF(OR(AND('Appendix B'!D8="(47780)",J19K190),AND(OR ('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'! D9="53S"),J19K190)),0,+J19K19) This OTHER formula works when P (just an examplesame formula is used for C and 53S. =IF(ISNUMBER(FIND("P",'Appendix B'!D9)),'Appendix B'! D42,0) I need to incorporte the IF(ISNUMBER(FIND("P",'Appendix B'! D9... formula into the existing 1st formula for C,P, 53S and can't do it. Please help if you can. Thanks in advance. . 
#4




Formula is sending me over the edge
Michael,
Because of your note, I had to rethink what I was doing and Lo and Behold, I got the formula to work It's below for your future reference. Thanks a million times over. =IF(OR(AND('Appendix B'!D8="(47780)",J19K190),(ISNUMBER (FIND("53",'Appendix B'!D9))),(ISNUMBER(FIND("C",'Appendix B'!D9))),(ISNUMBER(FIND("P",'Appendix B'!D9)))*AND(J19 K190)),0,+J19K19) Original Message If 47780 is in D8 and C, P, or 53S is in D9, what do you expect the result to be? Your current formula evaluates as TRUE if D8 contains 47780 and/or D9 contains C, P, or 53S. For any of these combinations, the result, in accordance with your formula, will be 0. If D8 does not contain 47780 and D9 does not contain C, P, or 53S then the formula evaluates as FALSE and J19K19 will be returned. Are you trying to come up with something else? Also, how do you want to incorporate the ISNUMBER formula into your original formula? What is the purpose? Post back and maybe we can find the right answer.  Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me."  AA Milne, Winnie the Pooh wrote in message ... This formula works when 47780 is input into D8, however, it does not work if C,P or 53S is in D9: =IF(OR(AND('Appendix B'!D8="(47780)",J19K190),AND(OR ('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'! D9="53S"),J19K190)),0,+J19K19) This OTHER formula works when P (just an examplesame formula is used for C and 53S. =IF(ISNUMBER(FIND("P",'Appendix B'!D9)),'Appendix B'! D42,0) I need to incorporte the IF(ISNUMBER(FIND("P",'Appendix B'! D9... formula into the existing 1st formula for C,P, 53S and can't do it. Please help if you can. Thanks in advance. . 
#5




Formula is sending me over the edge
" wrote...
Because of your note, I had to rethink what I was doing and Lo and Behold, I got the formula to work It's below for your future reference. Thanks a million times over. =IF(OR(AND('Appendix B'!D8="(47780)",J19K190), (ISNUMBER(FIND("53",'Appendix B'!D9))), (ISNUMBER(FIND("C",'Appendix B'!D9))), (ISNUMBER(FIND("P",'Appendix B'!D9))) *AND(J19K190)),0,+J19K19) Indenting terms with underscores, this becomes IF( __OR( ____AND( ______'Appendix B'!D8="(47780)", ______J19K190 ____), ____(ISNUMBER(FIND("53",'Appendix B'!D9))), ____(ISNUMBER(FIND("C",'Appendix B'!D9))), ____(ISNUMBER(FIND("P",'Appendix B'!D9)))*AND(J19K190) __), __0, __+J19K19 ) Do you really want to apply the AND(J19K190) test only when 'Appendix B'!D9 contains 'P'? That's what your formula does. Your original formula, =IF(OR(AND('Appendix B'!D8="(47780)",J19K190), AND(OR('Appendix B'!D9="C",'Appendix B'!D9="P", 'Appendix B'!D9="53S"),J19K190)),0,+J19K19) makes it appear you want to apply the J19K190 test to all 'Appendix B'!D9 cases. Starting with your original formula, you could try =IF(AND(J19K19,OR('Appendix B'!D8="(47780)", SUM(COUNTIF('Appendix B'!D9,"*"&{"C","P","53"}&"*")))),0,J19K19)  To toppost is human, to bottompost and snip is sublime. 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Offset formula  Frank Kabel  Worksheet Functions  5  March 15th, 2004 09:50 PM 
Dynamic formula to sum across rows  turtleman2  Worksheet Functions  5  March 8th, 2004 08:11 PM 
&[tab] Usage Twist  Jody  Worksheet Functions  4  January 10th, 2004 06:21 PM 
Sheet Names  Joseph M. Yonek  Worksheet Functions  6  January 3rd, 2004 02:15 AM 
evaluating formula  Jeff Rose  Worksheet Functions  1  September 26th, 2003 08:56 AM 