Formula Now Working

[Ron]

Hello all,

Any assistance with this formula appreciated. Trying to populate
cells in column G with "Comment Required" with the formula below. Any
assistance appreciated.

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

Results Cols for B:G, F is hidden.
16,670 52 (16,618) -32087.3% #VALUE!
- 6,785 6,785 100.0% #DIV/0!

Thank you,
Ron

 

[Ron]

Hello all,

Any assistance with this formula appreciated.  Trying to populate
cells in column G with "Comment Required" with the formula below.  Any
assistance appreciated.

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

Results Cols for B:G, F is hidden.
 16,670          52      (16,618)       -32087.3%      #VALUE!
 -       6,785   6,785  100.0%  #DIV/0!

Thank you,
Ron

 

[Pete_UK]

If you want to put two or more expressions together and AND them, the
syntax is:

AND(expr1,expr2,expr3 ...)

and there is also an OR function, used in a similar way. So, I think
what you are after is something like this:

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

and this could be shortened to:

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

Hope this helps.

Pete

 

[Ron]

Hi Pete, thank you for the formula fix. One problem I'm having is
this part of the formula is not catching the value in column C....
AND(B4=0,C4>10000)),"Comments Required in Column G","")

- 15,000 15,000


with this formula the result is "#DIV/0! if c4 = 0". So, I added to
your solution, please see the next formula.
=IF(OR(AND(D4/B4>=10%,D4>=10000),AND(D4/
B4<=-10%,D4<=-10000),AND(B4=0,C4>10000)),"Comments Required in Column
G","")


Now it's not addressing the amount in c4 because the 0 in b4 is
causing an error. Any ideas on how to address this issue is greatly
appreciated.
=IF(ISERROR(IF(OR(AND(D18/B18>=10%,D18>=10000),AND(D18/
B18<=-10%,D18<=-10000),AND(B18=0,C18>10000)),"Comments Required in
Column G","")),"",IF(OR(AND(D18/B18>=10%,D18>=10000),AND(D18/
B18<=-10%,D18<=-10000),AND(B18=0,C18>10000)),"Comments Required in
Column G",""))
AND(B4=0,C4>10000)),"Comments Required in Column G",""))


Thank you for your assistance, Ron

 

[Ron]

Hi Pete, thank you for the formula fix.  One problem I'm having is
this part of the formula is not catching the value in column C....
AND(B4=0,C4>10000)),"Comments Required in Column G","")

 -       15,000          15,000

with this formula the result is "#DIV/0! if c4 = 0".  So, I added to
your solution, please see the next formula.
=IF(OR(AND(D4/B4>=10%,D4>=10000),AND(D4/
B4<=-10%,D4<=-10000),AND(B4=0,C4>10000)),"Comments Required in Column
G","")

Now it's not addressing the amount in c4 because the 0 in b4 is
causing an error.  Any ideas on how to address this issue is greatly
appreciated.
=IF(ISERROR(IF(OR(AND(D18/B18>=10%,D18>=10000),AND(D18/
B18<=-10%,D18<=-10000),AND(B18=0,C18>10000)),"Comments Required in
Column G","")),"",IF(OR(AND(D18/B18>=10%,D18>=10000),AND(D18/
B18<=-10%,D18<=-10000),AND(B18=0,C18>10000)),"Comments Required in
Column G",""))
AND(B4=0,C4>10000)),"Comments Required in Column G",""))

Thank you for your assistance, Ron










- Show quoted text -

 

[Pete_UK]

Hi Ron,

There are two terms where you divide by B4, so both of these will give
the error if B4 is zero. Try it like this in G4:

=IF(AND(B4=0,C4>10000),"Comments Required",IF(B4=0,"",IF(OR(AND(D4/
B4>=10%,D4>=10000),AND(D4/B4<=-10%,D4<=-10000)),"Comments
Required","")))

Hope this helps.

Pete

 

[Ron]

Hi Pete, thank you for the solution. It works perfect. One condition
I missed, if C4 if less than or equal to -10000. So, if it's a 10,000
plus, minus variance a comment is required.

Your assistance has helped greatly, thank you. Ron

 

[Pete_UK]

You're welcome, Ron - thanks for feeding back.

Pete