Home

Breach or not breach result using IF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-780778%22%20slang%3D%22en-US%22%3EBreach%20or%20not%20breach%20result%20using%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780778%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3ECan%20anyone%20help%20to%20get%20result%20breach%20or%20not%20breach%20for%20the%20below%20condition%3CBR%20%2F%3E%3CBR%20%2F%3ETrader%20A%20limit%20%3D%20600%20for%20USD%2FAED%20and%20800%20for%20Other%20CCY%3CBR%20%2F%3ETrader%20B%20limit%20%3D%20700%20for%20USD%2FAED%20and%20600%20for%20Other%20CCY%3CBR%20%2F%3ETrader%20C%20limit%20%3D%20500%20for%20USD%2FAED%20and%20900%20for%20Other%20CCY%3CBR%20%2F%3E%3CBR%20%2F%3EType%20of%20contract%20%3D%20USD%2FAED%20or%20Other%20CCY%3CBR%20%2F%3E%3CBR%20%2F%3EColumn%20A%20trader%20name%3CBR%20%2F%3EColumn%20B%20amount%3CBR%20%2F%3EColumn%20C%20Type%20of%20contract%3CBR%20%2F%3E%3CBR%20%2F%3ECondition%3CBR%20%2F%3ELine%20no%201%20for%20Trader%20A%3CBR%20%2F%3EIf%20column%20C%20is%20USD%2FAED%20and%20amount%20%26gt%3B%3D600%20then%20Breach%3CBR%20%2F%3EIf%20column%20C%20is%20Other%20CCY%20and%20amount%20%26gt%3B%3D%20800%20then%20Breach%2C%20if%20not%20then%20Not%20Breach%3CBR%20%2F%3E%3CBR%20%2F%3ELine%202%2C%20Line%203%20and%20so%20on%20apply%20the%20limit%20condition%20as%20per%20Trader%20B%20and%20C%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20advice%2C%20I%20tried%20apply%20Nested%20if(and%20and%20result%20arrived%20successfully%20but%20few%20are%20incorrect%20not%20sure%20why%3F%20even%20though%20it%20match%20with%20other%20successful%20rows.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-780778%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780787%22%20slang%3D%22en-US%22%3ERe%3A%20Breach%20or%20not%20breach%20result%20using%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384711%22%20target%3D%22_blank%22%3E%40SSNU1312%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20greatly%20help%20if%20you%20upload%20a%20sample%20file%20with%20realistic%20data%2C%20the%20solution%20will%20be%20close%20to%20what%20you%20are%20looking%20for.%20please%20provide%20expected%20results%20too%20for%20few%20rows%20so%20that%20formula%20can%20be%20tested.%26nbsp%3B%20%26nbsp%3B%20thank%20you%20!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780805%22%20slang%3D%22en-US%22%3ERe%3A%20Breach%20or%20not%20breach%20result%20using%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384711%22%20target%3D%22_blank%22%3E%40SSNU1312%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%2C%20you%20are%20looking%20for%20something%20like%20that%20in%20the%20attached%20file.%20There%20are%20drop-down%20lists%20in%20Columns%20F%20and%20H%20for%20TraderName%20and%20ContractType%2C%20respectively.%20The%20formula%20in%20I2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(F2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EA%242%3AD%244%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMATCH(H2%2CA%241%3AD%241%2C0)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EConversly%2C%20the%20formula%20in%20J2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(G2%26gt%3B%3DI2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22Yes%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22No%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
SSNU1312
Occasional Visitor
Hi,
Can anyone help to get result breach or not breach for the below condition

Trader A limit = 600 for USD/AED and 800 for Other CCY
Trader B limit = 700 for USD/AED and 600 for Other CCY
Trader C limit = 500 for USD/AED and 900 for Other CCY

Type of contract = USD/AED or Other CCY

Column A trader name
Column B amount
Column C Type of contract

Condition
Line no 1 for Trader A
If column C is USD/AED and amount >=600 then Breach
If column C is Other CCY and amount >= 800 then Breach, if not then Not Breach

Line 2, Line 3 and so on apply the limit condition as per Trader B and C

Please advice, I tried apply Nested if(and and result arrived successfully but few are incorrect not sure why? even though it match with other successful rows.
2 Replies

@SSNU1312 

It would greatly help if you upload a sample file with realistic data, the solution will be close to what you are looking for. please provide expected results too for few rows so that formula can be tested.    thank you !!

Highlighted

@SSNU1312 

Perhaps, you are looking for something like that in the attached file. There are drop-down lists in Columns F and H for TraderName and ContractType, respectively. The formula in I2, copied down rows, is: 

=VLOOKUP(F2,
A$2:D$4,
MATCH(H2,A$1:D$1,0),0)

Conversly, the formula in J2, copied down rows, is: 

=IF(G2>=I2,
"Yes",
"No")

Related Conversations
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Which formula to use
Ramon Haagen in Excel on
2 Replies
Excel If Functions
Mfouad2255 in Excel on
10 Replies
function talking to table storage
donquijote in Compute on
0 Replies
Calculated column help
gopalaraoa in SharePoint on
1 Replies