EXCEL 97 if then
Solved/Closed
Mike K

Jan 31, 2010 at 03:48 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Feb 2, 2010 at 10:58 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Feb 2, 2010 at 10:58 AM
Related:
 EXCEL 97 if then
 Excel date format dd.mm.yyyy  Guide
 Transfer data from one excel worksheet to another automatically  Guide
 Excel conditional formatting if another cell contains specific text ✓  Excel Forum
 Excel if color is red then  Guide
 Number to words in excel  Guide
5 replies
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Jan 31, 2010 at 04:29 PM
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
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Jan 31, 2010 at 05:08 PM
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
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Jan 31, 2010 at 07:37 PM
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
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Feb 2, 2010 at 10:58 AM
Feb 2, 2010 at 10:58 AM
rizvisa@hotmail.com
Jan 31, 2010 at 04:50 PM
Thanks.
Mike K