Thread: Complex "IF" function

Attached is a sample of the spreadsheet I'm working on. The column I'm trying to amend is column O (within the Vertical Spreads tab). I have most of the formula correct, but I can't get the last few conditions correct...essentially, what I'm trying to do is write a formula that performs the following (I'll just write the whole thing out in plain english but you can see the formula I have so far in the spreadsheet):

If column G=0 and column H>0, or if column G>0 and column H=0, then multiply column D by -1.5, if both columns G and H are greater than 0, then multiply column D times 2 and then multiply by -1.5, if D=0, then do nothing

Thanks for your help!
- John

Thank you very much! How can I modify this formula so that it does not say, "FALSE" in column O when there is no value entered in column D?
- John

Thank you very much! I have one minor tweak I want to make to this function and I cannot figure it out. The additional "IF/AND" I need to add is (in plain english), IF column B is "BTC" and the amount in column J is equal to or less than .05, then do nothing. Because my spreadsheet has changed a little, I've reattached another sample. Thank you in advance!

- John

All,
Here is the formula I think is closest to the tweak I need to add in my most recent post:

IF(AND(B2="STC",J2<0.06),J2*0))

However, I either get a "too many functions for this formula" type of error or a general error in formula type of error.

What am I missing? Thanks!

- John

Try this formula in O2 copied down

=OR(B2<>"BTC",J2>0.05)*D2*COUNTIF(G2:H2,">0")*-1.5

Thank you! That worked! There is one other tweak that I want to make, but I'm going to rack my brains trying to figure it out before I make another post.
I appreciate your help!
- John

OK...I can't figure it out. How can I take the formula that Daddylonglegs supplied me with and add the additional criteria: if column D is blank, then show nothing.

This will prevent column O from being nothing but "$0.00" all the way down.

Thank you in advance...I appreciate your help!

- John

I tried adding to the function as you suggested but now when there is no value in Column D, I get the #VALUE! error in column O where I am putting the new formula.
Many thanks for your continued support! I will keep trying.
- John

EdMac,
Thank you very much for your help. Rather than just insert the red portion of the function you had suggested (which is what I did before), I just copied and pasted the whole thing into my spreadsheet and it works as advertised. I don't know what I did wrong, but obviously I missed something.
Thank you GREATLY for your assistance. This forum has been a tremendous help to me!
Cheers.
- John

I've been working at this for awhile taking cues from previous suggestions on this thread and I cannot work out the error. I've attached my spreadsheet for reference. The problem is with the "IF" function in column O. The formula is actually correct except for the "OR" contingent that states (B3<>"BTC",J3>0.05) -- I need to modify this by adding (in plain english): "and column H is blank" to it. I've tried but to no avail. I will post if I figure it out.
Thank you in advance for your help!
- John