Formula Now Working

  • Thread starter Thread starter Ron
  • Start date Start date
R

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
 
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
Click to expand...
 
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
 
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
 
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 -
Click to expand...
 
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
 
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
 
Back
Top