Combining IF formulas

Question

I have two IF formulas that I would like to combine - please see attached excel doc.

If C2 = "Blue" =IF(E2="","",IF(((((((B2*(C2-2))*1.02)/(E2-1))/1.02)+(-B2))+(B2))/(B2)<0.65,"NO BET",((C2-1)/(E2-F2)B2))) If C2 = "Green" =IF(E3="","",IF(((((((B3(C3-2))*1.02)/(E3-1))/1.02)+(-B3))+(B3))/(B3)<0.65,"NO BET",(C3/(E3-F3)*B3)))

The formulas are the same up until after "NO BET". I would like this to be one formula only so that I can change value C2 and it calculates correctly.

Many thanks

Answer 1

I don't have the rep required to comment for clarifications so I've got a couple of possible options depending on what you need. I've also imposed a bit of spacing in my answer so its a bit more readable.

If C2 = "Blue" =IF(E2="","",IF(((((((B2*(C2-2))*1.02)/(E2-1))/1.02)+(-B2))+(B2))/(B2)<0.65,"NO BET",((C2-1)/(E2-F2)B2)))

If C2 = "Green" =IF(E3="","",IF(((((((B3(C3-2))*1.02)/(E3-1))/1.02)+(-B3))+(B3))/(B3)<0.65,"NO BET",(C3/(E3-F3)*B3)))

In the question you say that the formulas are the same up to NO BET so, assuming that:

• you want both forumlas to work on both rows 2 and 3 and
• the C column is filled in Green/Blue for all the different rows

this is how it can work for row 2:

=IF(E2="",
    "",
    IF(
        ((((((B2*(C2-2))*1.02)/(E2-1))/1.02)+(-B2))+(B2))/(B2)<0.65,
        "NO BET",
        IF(C2 = "Blue", 
           ((C2-1)/(E2-F2)B2),
           IF(C2 = "Green", 
              (C2/(E2-F2)*B2)
              )
           )
        )
    ) 

If Blue/Green is only ever in C2 and the rest of the C column is irrelevent

=IF(E2="",
    "",
    IF(
        ((((((B2*(C2-2))*1.02)/(E2-1))/1.02)+(-B2))+(B2))/(B2)<0.65,
        "NO BET",
        IF($C$2 = "Blue", 
           ((C2-1)/(E2-F2)B2),
           IF($C$2 = "Green", 
              (C2/(E2-F2)*B2)
              )
           )
        )
    )