Needs help please, editing or creating a formula. Thank you!!!

[wenchita1019]

Hi,

I need help with a formula that meets the following conditions.

Result as “Y” in a cell when meeting all of the following conditions.
CA3<200
CB3<230
CD3<CE3*0.35
CF3<CG3*0.1 or CF3<1

<ce3*0.35
<ce3

<ce3*0.35
<cg3*0.1 or="" cf<1="" (either="" one="" of="" these="" conditions)<="" strong="">
</cg3*0.1><cg3*0.1 or="" cf<1="" (either="" one="" of="" these="" conditions)<="" strong="">Otherwise, the result should be “N”.

I came up with the following...
=IF(CA3>200,”N”,IF(CB3>230,”N”,IF(CD3>CE3*0.35,”N”,”Y”)))

However, I don't know how to add the last one </cg3*0.1></ce3*0.35
</ce3
</ce3*0.35
(CF3<CG3*0.1 or CF3<1)
<ce3*0.35
<ce3
<ce3*0.35
<cg3*0.1 or="" cf<1="" (either="" one="" of="" these="" conditions)<="" strong=""><cg3, or="" cf<1)<cg3*0.1="" cf<1)
<cg3*0.1 or="" cf3<1)="" into="" the="" same="" formula.

All the amazing excel wizards out there, could someone please kindly help me with this?

Thank you! Thank you! Thank you!
Wenchita

</cg3*0.1></cg3,></cg3*0.1><cg3*0.1 or="" cf<1="" (either="" one="" of="" these="" conditions)<="" strong="">



</cg3*0.1><cg3*0.1 or="" cf<1="" (either="" one="" of="" these="" conditions)<="" strong=""></cg3*0.1></ce3*0.35
</ce3
</ce3*0.35

 

[wenchita1019]

Sorry, I am having some trouble with this post. It would not post all the text I entered.



Hi,

I need help with a formula that meets the following conditions.

Result as “Y” in a cell when meeting all of the following conditions.
CA3<200
CB3<230

CD3<CE3*0.35
CF3<CG3*0.1 or CF3<1 (Either one of these conditions)
Otherwise, the result should be “N”.

I came up with the following...
=IF(CA3>200,”N”,IF(CB3>230,”N”,IF(CD3>CE3*0.35,”N”,”Y”)))

However, I don't know how to add the last one (CF3<CG3*0.1 or CF3<1)

All the amazing excel wizards out there, could someone please kindly help me with this?

Thank you! Thank you! Thank you! :wink:
Wenchita

 

[RonB1111]

I understand the last CF3 didn't come thru. Let me assume it's CF3>CG3*20. Just insert another IF statement in front of your last Y, like this:

[SIZE=2][COLOR=#000000][FONT=Arial]=IF(CA3>200,”N”,IF(CB3>230,”N”,IF(CD3>CE3*0.35,”N”,[/FONT][/COLOR][B][COLOR=#ff0000][FONT=Arial]if(CF3>CG3*20,"N",[/FONT][/COLOR][/B][COLOR=#000000][FONT=Arial]”Y”)))[/FONT][/COLOR][/SIZE][B][COLOR=#ff0000])[/COLOR][/B]

 

[wenchita1019]

RonB1111,

I am truly grateful for your reply. The last CF3 actually has two conditions. If either one of those condition is true then it could be "Y", as long as all the other are true as well.

I think half of below went through.
CF3<...<cg3
<cg3*20 <cg*20="" or="" cf3<20

or
CF3<1
How would I add that in the formula?

Thank you! Your thought and input is greatly appreciated.
Wenchita</cg3*20></cg3

 

[RonB1111]

........ If either one of those condition is true then it could be "Y", as long as all the other are true as well.

<cg3

</cg3
I'm not following the logic in the statement in red above. In any event, you'd use either "AND()" or "OR()" within the IF formula, something like this:

[SIZE=2][COLOR=#000000][FONT=Arial]
with OR():    =IF(CA3>200,”N”,IF(CB3>230,”N”,IF(CD3>CE3*0.35,”N”,[/FONT][/COLOR][B][COLOR=#ff0000][FONT=Arial]if(OR(CF3>CG3*20,CF3<1),"N",[/FONT][/COLOR][/B][COLOR=#000000][FONT=Arial]”Y”)))[/FONT][/COLOR][/SIZE][COLOR=#ff0000])[/COLOR][SIZE=2][COLOR=#000000][FONT=Arial]
with AND()   =IF(CA3>200,”N”,IF(CB3>230,”N”,IF(CD3>CE3*0.35,”N”,[/FONT][/COLOR][B][COLOR=#ff0000][FONT=Arial]if(AND(CF3>CG3*20,CF3<1),"N",[/FONT][/COLOR][/B][COLOR=#000000][FONT=Arial]”Y”)))[/FONT][/COLOR][/SIZE][COLOR=#ff0000])[/COLOR]

When you post a formula on this forum, you should highlight(select) the formula and then click the # icon in the toolbar at the top of the Reply box - this will wrap the formula in Code tags and hopefully will display your entire formula.
If you can't get the formula to work, please explain the logic and try providing the full formula wrapped in Code tags again.

 

[wenchita1019]

Hi,

Thank you for your reply. I tried this, but not sure if it works.

( =IF(AND(CA3<200,CB3<230,CD3<ce3*0.35,if(or(cf3<cg3*20,cf3<1),"y","n")))
<CE3*0.35,IF(OR(CF3<CG3*20,CF3<1),"Y","N"))) )

<ce3*0.35,if(or(cf3<cg3*20,cf3<1),"y","n"))))
If all other conditions are true, and either one of CF3's condition is also true, it should result as "Y". Otherwise, "N".
So CF3 doesn't need to be meet both conditions,

(<CG3*20 )

and

( <1 )

<cg3*20) and="" (<1).<cg3*20="" cf3<1.=""

Does that make better sense?

Thank you so much for taking your time.
wenchita</cg3*20)></ce3*0.35,if(or(cf3<cg3*20,cf3<1),"y","n"))))
</ce3*0.35,if(or(cf3<cg3*20,cf3<1),"y","n")))

 

[wenchita1019]

My apology again. That didn't seem to come through either.

This is what I am going for, using both IF(AND & IF(OR.

with AND() plus OR()
=IF(AND(CA3<200,CB3<230,CD3<CE3*0.35,IF(OR(CF3<CG*20,CF3<1),"Y","N")))

Sorry, still not coming through~~
wenchita
</pre>

 

[RonB1111]

Unfortunately the formulas still didn't come thru even though you've used the code tags. This has happened to me a few times & the only way I could get them to post right (even with the code tags) is to put a spacebar before and after each special character like = > < * + - .

Based on your statement ("If all other conditions are true, and either one of CF3's condition is also true, it should result as "Y". Otherwise, "N"") as well as what portion of the formula did come thru, the logic has changed from your 1st post. In your 1st post, if any condition was true, then the result was "N". So assuming you want the logic in your most recent post, you can nest an OR within an AND where the formula might look something like this:

[SIZE=2][FONT=Arial]=IF(AND(CA3>200,CB3>230,CD3>CE3*0.35,[/FONT][B][FONT=Arial]OR(CF3>CG3*20,CF3<1)),"Y",[/FONT][/B][FONT=Arial]”N”)[/FONT][/SIZE]

I'm also experiencing some odd behavior today with the CODE tags, so try adding spaces before and after special characters in your formula.

Edit - for some reason, your post #7 did not display for me until after I made this post - I think everything in this post still applies.

 

[wenchita1019]

Thank you again for your input. This is a full version of what I'm working on. Does it look right?

I really hope it can come through this time.

= IF(OR(CA3 = "LOI",CA3 = ""),"LOI",IF(BW3 < = 2,IF(AND(CL3 * 9 < = CK3 * 0.35,CN3 = 0,CP3 < = 230,CK3 < = 200,CS3 < = BX3 * 0.35,OR(CM3 * 9 < = CK3 * 0.1,CM3 < = 1),"Y")),IF(AND(CA3 * 9 < = BZ3 * 0.35,CC3 = 0,CE3 < = 230,BZ3 < = 200,CH3 < = BY3 * 0.35,OR(CB3 * 9 < = BZ3 * 0.1,CB3 < = 1),"Y",”N”))))

 

[RonB1111]

See if this helps:

= IF(
OR(CA3 = "LOI",CA3 = ""),"LOI",
IF(BW3 < = 2,
IF(
AND(CL3 * 9 < = CK3 * 0.35,CN3 = 0,CP3 < = 230,CK3 < = 200,CS3 < = BX3 * 0.35,OR(CM3 * 9 < = CK3 * 0.1,CM3 < = 1) [COLOR=#ff0000][I][B]missing ) to close the AND[/B][/I][/COLOR]
,"Y"  [COLOR=#ff0000][I][B]missing what to do if false[/B][/I][/COLOR]  ))   [COLOR=#ff0000][I][B]one ) should go earlier for AND[/B][/I][/COLOR]  ,
IF(AND(CA3 * 9 < = BZ3 * 0.35,CC3 = 0,CE3 < = 230,BZ3 < = 200,CH3 < = BY3 * 0.35,OR(CB3 * 9 < = BZ3 * 0.1,CB3 < = 1)   [COLOR=#ff0000][B]missing ) to close the AND[/B][/COLOR]  ,"Y",”N”)))) [I][B][COLOR=#ff0000]one too many ) should probably go to AND[/COLOR][/B][/I]

I'm not sure what your intent is with both the 2nd and 3rd IF statements. You've closed the 3rd IF after the AND but didn't show what should be returned if the AND was FALSE. I'd suggest fixing the closing )'s for the AND statements so that the formula will run and then test with various values in the affected cells to see if it's return the answers you expect.