Formula,Function, or VBA

[Ron]

Hello all, I'm attempting to require comments in column g for
variances in column d. These conditions would require coments.... if
d6 is is greater than or equal to 10% of b6, and greater than or equal
to 10,000, and if b6 equals 0 and c6 is greater than or equal to
10,000.

I would like to populate column G with "COMMENTS REQUIRED" or, "",
when the conditions above are met instead of False, #Div/0!, or True
that I get with the formula below.

=IF((D6/B6)>=10%,AND(D6>=10000))=IF((D6/
B6)<=-10%,AND(D6<=-10000))=IF(B6=0,AND(C6>10000))

1,920 995 (926) -93.1% FALSE
- 6,785 6,785 100.0% #DIV/0!
6,201 258 (5,943) -2307.6% FALSE
16,670 52 (16,618) -32087.3% TRUE


Thank you for your assistance,
Ron

 

[Paul Robinson]

Hi Ron
I think you will have to sort the logic out before the formula. In a
formula, AND means one thing is true AND another thing is true at the
same time. In your question you have "and b6 equals 0" along with the
other ands. Then "d6 is greater than or equal to 10% of b6" won't make
much sense since b6 is 0. You really need some logical OR bits along
with your AND's. Then you can knit your conditions together with OR
(this is true OR this is true OR...). In English, we often say and
when we really mean logical OR. In your question, I can't tell where
you mean an OR and when you mean an AND.
In your formula the syntax is also wrong. If you want "d6 is is
greater than or equal to 10% of b6, AND (d6 is) greater than or equal
to 10,000" you would write
AND(D6/B6>=0.1, D6>=10000)

the AND is not between the two inequalities as you have it. OR works
the same way.

See if you can reword your problem statement in terms of logical AND
and OR and post back. It should end up looking something like
If either this AND this AND this... is True, OR this AND this AND
this... is True, OR...then comments are required. The formula should
be easy then.

regards
Paul