Looking for an improved formula

[Stan101]

Hi am trying to check conditions of various cells to decide on the correct calculation. I have come up with the following:

=IF(AND('Cover Sheet'!B12="yes",($C$2-$B8)/0.2=INT(($C$2-$B8)/0.2)),B8*(C8+0.2),IF(AND('Cover Sheet'!B12="yes",($C$2-$B8)/0.2<>INT(($C$2-$B8)/0.2)),B8*(C8+0.1),IF(AND('Cover Sheet'!B12<>"yes",($C$2-$B8)/0.2<>INT(($C$2-$B8)/0.2)),B8*(C8+0.1),B8*C8)))

Basically, I am checking the condition of cell B12 on the cover sheet, and the condition if C2 - B8 has a remainder or not and then to make a calc based on the outcome . But it is quite a cumbersome formula with only 2 conditions and if I need to check more conditions - which I will - the whole formula gets much more complicated. Is there a better way, via formula or VBA that can make this style of formula easier?

I also will have many instances in this workbook and others where I need to check such conditions.

Any suggestions to make this more graceful?

Also, I didn't use MOD in my formula because it sometimes gives me strange results when using decimal numbers.

 

[jasonb75]

You don't need to repeat logical tests if you set them out in a logical order. Anything that is not ="yes" must be <>"yes" by process of elimination so it doesn't need to be checked twice.
Also, by using AND, you need to check ="yes" twice, without AND you only need to check it once, then follow it with the checks for the value calculation.

I've trimmed it down based on what you've shown in your example formula, but without a list of all conditions (not a formula containing them) to work with, I can't say for sure that this will still work when you try to expand it.

=B8*(C8+IF(($C$2-$B8)/0.2=INT(($C$2-$B8)/0.2),IF('Cover sheet'!B12="yes",0.2),0.1))

 

[Stan101]

Jason, thank you. Your formula is much more elegant. I had never considered using the if statement in an equation like that. And if I ever saw it used, I never acknowledged it. I never stop learning here.

I also think I used the term "condition" incorrectly. I wasn't looking for a true or false value.