EXCEL 97 if thenSolved/Closed

Question

Hello,

I would like a Cell "1" to look at two Cells, Cell "2" and Cell "3" and depending on the answer enter a positive or negative vaue of Cell "4" in Cell "1". Is the possible? Below is what I tried.

=IF(F4="P",D4," ")=IF(G4="P",-D4," ")

Thank you.

Mike K

rizvisa1 · Answer

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)

rizvisa1 · Answer

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

Mike K · Answer

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

rizvisa1 · Answer

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 " "

rizvisa1 · Answer

rizvisa@hotmail.com

EXCEL 97 if then

5 responses

Similar discussions

Subscribe To Our Newsletter!