Jamal, let me try. One thing I see wrong in your attachment is the way you're attempting to do "and" conditions:
Code:
=IF((A2>0&B2>0),A2+B2, 0)
In an Excel formula, "&" is the string-concatenation symbol, not the logical AND operator. So what does Excel see here? I've done a little testing and I think it's this:
Code:
(A2 > (0 & B2)) > 0
That is, first it concatenates 0 to B2. In your sample worksheet that yields the string "011". Then it checks to see whether A2 (which has the value 1) is greater than "011". Excel decides this is FALSE. Lastly it decides whether the result FALSE is greater than 0, which is TRUE. That's why you're always getting A+B in your worksheet.
To do an AND condition, change your formula to do this:
Code:
=IF(AND(A2>0,B2>0),A2+B2,0)
Maybe that's all you need; if you now know how to proceed, you're on your way. But if you're still having trouble, here are some comments:
1) You tried three time to explain to Pecoflyer what you want to accomplish, and although the explanations look clear enough to me, I do notice that you said it differently each time. The first time, on 07-28, you said:
A>0, B>0 => A+B
A>0, B<0 => A-B
A<0, B>0 => 2A
A<0, B<0 => 3B-9
The next day you changed what happened when A is negative:
A<0, B>0 => =2A-B
A<0, B<0 => 3B-A
The third time, 07-30, you just gave constants:
A>0, B>0 => 10
A>0, B<0 => 20
A<0, B>0 => 30
A<0, B<0 => 40
I think I know why you did that: You already know how to do the arithmetic calculation, but you wanted to ask only about the IF formula. But if there's another reason, what is it?
2) If you're not sure how to combine many IF statements together, here's one sample. It uses your first set of calculations, and if any of the values in A or B is
equal to 0 (rather than greater or less) then it displays a blank value:
Code:
=IF(AND($A2>0,$B2>0),$A2+$B2,IF(AND($A2>0,$B2<0),$A2-$B2,IF(AND($A2<0,$B2>0),$A2*2,IF(AND($A2<0,$B2<0),3*$B2-9,""))))
That's doing it the longest possible way. If you know that none of your values can ever be equal to 0, you can make it shorter:
Code:
=IF($A2>0,IF($B2>0,$A2+$B2,$A2-$B2),IF($B2>0,$A2*2,$B2*3-9))
If you like being clever—and I do—you can do it a little differently. Instead of doing multiple IFs and ANDs, consider turning the four possible combinations into choices 1, 2, 3 and 4. For this the calculation is
Code:
=(1-SIGN(A2))+(1-SIGN(B2))/2+1
What good is that?, you ask? Well, now you can tell Excel to CHOOSE which of four formula to apply:
Code:
=CHOOSE((1-SIGN(A2))+(1-SIGN(B2))/2+1,A2+B2,A2-B2,A2*2,B2*3-9)
The index calculation is harder to read than the IF and AND formulae, but the final calculations are lined up and maybe it's easier to pick them out this way.
Feel free to ask more questions, but I hope this helps out some.