IF and AND

[Guest]

I need to create a formula that has an IF condition and an AND condition.
This is what I would ultimately like: =IF(AND(B4>8,B4-8,C4="")B4>8,B4-8,0)

It needs to look at cell B4 figure out if it is greater than 8 then subtract
8 from B4 but only if it cell C4 is blank. If C4 is not blank it needs leave
the value from cell B4 alone.

Can you help?

Thanks!

 

[JE McGimpsey]

One way:

=B4-IF(AND(B4>8,C4=""),8,0)

or, equivalently

=B4-IF(OR(B4<=8,C4<>""),0,8)

or, equivalently

=B4-8*AND(B4>8,C4="")

or, equivalently

=B4-8*(B4>8)*(C4="")

 

[RWN]

How about;

=IF(AND(B4>8,C4=""),B4-8,0)

 

[Buck]

Excel doesn't like circular references, so I'm not sure you can change
the value of B4 based on an evaluation of B4.

Perhaps you could put the following formula in any other clear cell and
then base the rest of your calculations of that reference?
=IF(AND(B4>8,C4="",B4-8,"B4")

 

[Jerry]

Ashley,

On the microsoft site i found this tid bit.

you can use the + and * operators to perform or and and functions.

IF(((G2="CJA")*(G2="EVT")+(C2<>"CC")),"U",IF(C2="CC","","R"))

The conditional part of the if statement reads as follows:
if cell G2 equals CJA or G2 equals EVT and C2 is not equal to CC then U,
else next if statement.

Hope this helps.
Jerry

 

[Jerry]

Try this

=IF(((B4>8)*(C4="")),B4-8,0) it works according to your specifications.

Jerry