EXCEL 97 if then
Solved/Closed
Mike K

Jan 31, 2010 at 03:48 PM
rizvisa1
rizvisa1
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Related:
 EXCEL 97 if then
 Transfer data from one excel worksheet to another automatically  Guide
 How to change date format in excel  Guide
 Excel @ in formula ✓  Forum  Excel
 How to change author name in excel  Guide
 Compare two worksheets and paste differences to another sheet  excel vba free download ✓  Forum  Excel
5 replies
rizvisa1
Jan 31, 2010 at 04:29 PM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Jan 31, 2010 at 04:29 PM
This says, if B1 and C1 are both > 0, then use d1 or use negative of d1
=IF( AND(B1>0, C1>0), D1, D1)
This says, if B1>0 OR C1>0 OR both > 0, then use d1 or use negative of d1
=IF( OR(B1>0, C1>0), D1, D1)
=IF( AND(B1>0, C1>0), D1, D1)
This says, if B1>0 OR C1>0 OR both > 0, then use d1 or use negative of d1
=IF( OR(B1>0, C1>0), D1, D1)
rizvisa1
Jan 31, 2010 at 05:08 PM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Jan 31, 2010 at 05:08 PM
Mike, still same would be true. In that case you would be saying some thing below
this says if b1 and c1 are "", then a1 should be ""
else if both B1 and c1 contain c,p,s,cr,pc or ps, then use positive of D1 else use negative of d1
=IF(AND(B1="", C1=""), "", IF( AND(OR(B1="C", B1="P", B1="S", B1="CR", B1="PC", B1="PS"), OR(C1="C", C1="P", C1="S", C1="CR", C1="PC", C1="PS")), D1, D1) )
If it is still not what you were looking for, could your give an exact sample how data looks like and what is your expectation
this says if b1 and c1 are "", then a1 should be ""
else if both B1 and c1 contain c,p,s,cr,pc or ps, then use positive of D1 else use negative of d1
=IF(AND(B1="", C1=""), "", IF( AND(OR(B1="C", B1="P", B1="S", B1="CR", B1="PC", B1="PS"), OR(C1="C", C1="P", C1="S", C1="CR", C1="PC", C1="PS")), D1, D1) )
If it is still not what you were looking for, could your give an exact sample how data looks like and what is your expectation
rizvisa1,
Again thank you.
What I am trying to do is create a spread sheet for my budget.
I want to list my Transactions in "Cell B4" then by use of this formula with the entry of one of those letters in either a "Paid Colomn F4" or "Recieved Column H4" take the value entered in the "Amount Column D4" and enter it as a positive or negative value in this Column that is referenced by the letters "C, P, S, CR, PC, or PS" where this formula resides. These letter columns reference different accounts and or cash.
Mike K
Again thank you.
What I am trying to do is create a spread sheet for my budget.
I want to list my Transactions in "Cell B4" then by use of this formula with the entry of one of those letters in either a "Paid Colomn F4" or "Recieved Column H4" take the value entered in the "Amount Column D4" and enter it as a positive or negative value in this Column that is referenced by the letters "C, P, S, CR, PC, or PS" where this formula resides. These letter columns reference different accounts and or cash.
Mike K
rizvisa1
Jan 31, 2010 at 07:37 PM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Jan 31, 2010 at 07:37 PM
Mike, I must be missing some thing again coz i think the last formula would do that
See if I understood you correctly
1. The formula needs to be at cell B4
2. The letters would be either in cell F4 or H4
3. The amount would be in d4
4. Based on letters in cells F4 and H4, you want to put in b4 the value of D4 (positive or negative depending on H4 and F4)
5. In case F4 and H4 are blank, the B4 should be blank
=IF(AND(F4="", h4=""), "", IF( AND(OR(F4="C", F4="P", F4="S", F4="CR", F4="PC", F4="PS"), OR(H4="C", H4="P", H4="S", H4="CR", H4="PC", H4="PS")), D4, D4) )
1. AND(F4="", h4=""), "" part is saying that if both f4 and h4 are blank, then leave b4 blank too.
2. OR(F4="C", F4="P", F4="S", F4="CR", F4="PC", F4="PS") is checking if F4 contains any of those letters
3. OR(H4="C", H4="P", H4="S", H4="CR", H4="PC", H4="PS") is checking if H4 contains any of those letters
4. IF( AND(OR(...),OR(..)) is saying that both OR condition must be true, that is, both f4 and h4 must have one of those letters
5. , D4, D4 part is saying that if #4 is true, the b4 = d4 and if #4 is false b4=d4
I do think that you are telling me that some of those letters means that value should be positive and other letters means negative. Since I dont know which letter mean what, I did not put it. If for negatives, you can just remove them from the list or add one more if clause where D4 was being flagged in the formula.
If still it does not help, may be you can explain in words what your were trying to do here. =IF(F4="P",D4," ")=IF(G4="P",D4," ")
I think thats what you were trying to say, but I could be wrong
if F4=p, then d4 else if g4=p then d4 and if not both then " "
See if I understood you correctly
1. The formula needs to be at cell B4
2. The letters would be either in cell F4 or H4
3. The amount would be in d4
4. Based on letters in cells F4 and H4, you want to put in b4 the value of D4 (positive or negative depending on H4 and F4)
5. In case F4 and H4 are blank, the B4 should be blank
=IF(AND(F4="", h4=""), "", IF( AND(OR(F4="C", F4="P", F4="S", F4="CR", F4="PC", F4="PS"), OR(H4="C", H4="P", H4="S", H4="CR", H4="PC", H4="PS")), D4, D4) )
1. AND(F4="", h4=""), "" part is saying that if both f4 and h4 are blank, then leave b4 blank too.
2. OR(F4="C", F4="P", F4="S", F4="CR", F4="PC", F4="PS") is checking if F4 contains any of those letters
3. OR(H4="C", H4="P", H4="S", H4="CR", H4="PC", H4="PS") is checking if H4 contains any of those letters
4. IF( AND(OR(...),OR(..)) is saying that both OR condition must be true, that is, both f4 and h4 must have one of those letters
5. , D4, D4 part is saying that if #4 is true, the b4 = d4 and if #4 is false b4=d4
I do think that you are telling me that some of those letters means that value should be positive and other letters means negative. Since I dont know which letter mean what, I did not put it. If for negatives, you can just remove them from the list or add one more if clause where D4 was being flagged in the formula.
If still it does not help, may be you can explain in words what your were trying to do here. =IF(F4="P",D4," ")=IF(G4="P",D4," ")
I think thats what you were trying to say, but I could be wrong
if F4=p, then d4 else if g4=p then d4 and if not both then " "
Didn't find the answer you are looking for?
Ask a question
rizvisa1
Feb 2, 2010 at 10:58 AM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Feb 2, 2010 at 10:58 AM
rizvisa@hotmail.com
Jan 31, 2010 at 04:50 PM
Thanks.
Mike K