A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula is sending me over the edge



 
 
Thread Tools Display Modes
  #1  
Old June 24th, 2004, 04:04 PM
external usenet poster
 
Posts: n/a
Default 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)",J19-K190),AND(OR
('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'!
D9="53S"),J19-K190)),0,+J19-K19)

This OTHER formula works when P (just an example--same
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  
Old June 24th, 2004, 06:40 PM
Michael Malinsky
external usenet poster
 
Posts: n/a
Default 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
J19-K19 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)",J19-K190),AND(OR
('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'!
D9="53S"),J19-K190)),0,+J19-K19)

This OTHER formula works when P (just an example--same
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  
Old June 24th, 2004, 07:36 PM
external usenet poster
 
Posts: n/a
Default 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 J19-K19 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
J19-K19 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)",J19-K190),AND(OR
('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'!
D9="53S"),J19-K190)),0,+J19-K19)

This OTHER formula works when P (just an example--same
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  
Old June 25th, 2004, 03:52 PM
external usenet poster
 
Posts: n/a
Default Formula is sending me over the edge

Michael,

Because of your note, I had to re-think 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)",J19-K190),(ISNUMBER
(FIND("53",'Appendix B'!D9))),(ISNUMBER(FIND("C",'Appendix
B'!D9))),(ISNUMBER(FIND("P",'Appendix B'!D9)))*AND(J19-
K190)),0,+J19-K19)


-----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
J19-K19 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)",J19-K190),AND(OR
('Appendix B'!D9="C",'Appendix B'!D9="P",'Appendix B'!
D9="53S"),J19-K190)),0,+J19-K19)

This OTHER formula works when P (just an example--same
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  
Old June 25th, 2004, 07:52 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default Formula is sending me over the edge

" wrote...
Because of your note, I had to re-think 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)",J19-K190),
(ISNUMBER(FIND("53",'Appendix B'!D9))),
(ISNUMBER(FIND("C",'Appendix B'!D9))),
(ISNUMBER(FIND("P",'Appendix B'!D9)))
*AND(J19-K190)),0,+J19-K19)


Indenting terms with underscores, this becomes

IF(
__OR(
____AND(
______'Appendix B'!D8="(47780)",
______J19-K190
____),
____(ISNUMBER(FIND("53",'Appendix B'!D9))),
____(ISNUMBER(FIND("C",'Appendix B'!D9))),
____(ISNUMBER(FIND("P",'Appendix B'!D9)))*AND(J19-K190)
__),
__0,
__+J19-K19
)

Do you really want to apply the AND(J19-K190) test only when 'Appendix B'!D9
contains 'P'? That's what your formula does. Your original formula,

=IF(OR(AND('Appendix B'!D8="(47780)",J19-K190),
AND(OR('Appendix B'!D9="C",'Appendix B'!D9="P",
'Appendix B'!D9="53S"),J19-K190)),0,+J19-K19)

makes it appear you want to apply the J19-K190 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,J19-K19)

--
To top-post is human, to bottom-post and snip is sublime.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 08:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.