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

wenchita1019

New Member
Joined
Jun 12, 2014
Messages
11
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
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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:
Code:
[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]
 
Upvote 0
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
 
Last edited:
Upvote 0
........ 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:
Code:
[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.
 
Last edited:
Upvote 0
Hi,

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

Code:
( =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,
Code:
(<CG3*20 )
and
Code:
( <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")))
 
Last edited:
Upvote 0
My apology again. That didn't seem to come through either.

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


Code:
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>
 
Last edited:
Upvote 0
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:
Code:
[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.
 
Last edited:
Upvote 0
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.

Code:
= 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”))))
 
Upvote 0
See if this helps:
Code:
= 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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top