SOLVED

Identify a mismatch based on a range

%3CLINGO-SUB%20id%3D%22lingo-sub-2007111%22%20slang%3D%22en-US%22%3EIdentify%20a%20mismatch%20based%20on%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2007111%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20below%20data%20of%20prices.%20There%20are%20prices%20in%20which%20there%20is%20an%20insignificant%20difference%20i.e.%201%20penny.%20I%20need%20a%20formula%20to%20identify%20prices%20that%20there%20is%20a%20significant%20difference%20say%20by%20more%20than%20a%20full%20dollar%20and%20insignificant%20ones%20i.e.%20anything%20less%20than%20a%20dollar.%20and%20present%20it%20as%20is%20below.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22265%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2293%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2289%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2283%22%3EC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EVendor%201%3C%2FTD%3E%3CTD%3EVendor%202%3C%2FTD%3E%3CTD%3EDifference%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1236.83%3C%2FTD%3E%3CTD%3E1199.72%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1562.31%3C%2FTD%3E%3CTD%3E1359.26%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1669.45%3C%2FTD%3E%3CTD%3E1669.44%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1842.72%3C%2FTD%3E%3CTD%3E1789.1%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1707.92%3C%2FTD%3E%3CTD%3E1656.67%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1046.65%3C%2FTD%3E%3CTD%3E1046.64%3C%2FTD%3E%3CTD%3EInsignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1533.43%3C%2FTD%3E%3CTD%3E1489.1%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2153.16%3C%2FTD%3E%3CTD%3E2090.25%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1866.01%3C%2FTD%3E%3CTD%3E1866%3C%2FTD%3E%3CTD%3EInsignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2211.15%3C%2FTD%3E%3CTD%3E2211.14%3C%2FTD%3E%3CTD%3EInsignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2220.81%3C%2FTD%3E%3CTD%3E2220.8%3C%2FTD%3E%3CTD%3EInsignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2028.01%3C%2FTD%3E%3CTD%3E1968.85%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2943.48%3C%2FTD%3E%3CTD%3E2547.07%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3441.75%3C%2FTD%3E%3CTD%3E2959.89%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2366.53%3C%2FTD%3E%3CTD%3E2050.9%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2769.74%3C%2FTD%3E%3CTD%3E2688.32%3C%2FTD%3E%3CTD%3ESignificant%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3789.59%3C%2FTD%3E%3CTD%3E3789.58%3C%2FTD%3E%3CTD%3EInsignificant%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2007111%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%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-2007588%22%20slang%3D%22en-US%22%3ERe%3A%20Identify%20a%20mismatch%20based%20on%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2007588%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(A2-B2%26lt%3B1%2C%22inSignificant%22%2C%20%22Significant%22)%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26lt%3B1%3C%2FSTRONG%3E%20mean%20the%20difference%20between%20the%20two%20prices%20is%20less%20than%201%20%2C%20so%20you%20can%20change%20the%20number%20that%20fits%20your%20needs%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello,

 

I have the below data of prices. There are prices in which there is an insignificant difference i.e. 1 penny. I need a formula to identify prices that there is a significant difference say by more than a full dollar and insignificant ones i.e. anything less than a dollar. and present it as is below.   

 

ABC
Vendor 1Vendor 2Difference
1236.831199.72Significant
1562.311359.26Significant
1669.451669.44Significant
1842.721789.1Significant
1707.921656.67Significant
1046.651046.64Insignificant
1533.431489.1Significant
2153.162090.25Significant
1866.011866Insignificant
2211.152211.14Insignificant
2220.812220.8Insignificant
2028.011968.85Significant
2943.482547.07Significant
3441.752959.89Significant
2366.532050.9Significant
2769.742688.32Significant
3789.593789.58Insignificant
1 Reply
Best Response confirmed by shade206 (Contributor)
Solution

Hi @shade206 

 

You can use this formula:

=IF(A2-B2<1,"inSignificant", "Significant")

<1 mean the difference between the two prices is less than 1 , so you can change the number that fits your needs