How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

**darrenj1471** · 07-03-2023, 05:24 AM

Hello

I hope you can help me. I have a column with values and another with the following months values. I want to insert a new column and formula which compares the 2 values and states Yes or No to indicate whether the newer value is within 10% +/- tolerance. I know its some form of IF statement but dont know how or if possible?

Thanks in advance

**AliGW** · 07-03-2023, 05:27 AM

Still on Excel 2016?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**Glenn Kennedy** · 07-03-2023, 05:28 AM

One way:

=IF(ABS((B2-A2)/A2)<0.1,"Yes","No")

**Pete_UK** · 07-03-2023, 05:29 AM

What columns are you using? Assuming A and B for the values, then you can use this in C2:

=IF(ABS(A2-B2)/A2<0.1,"Y","N")

Copy down as required.

Hope this helps.

Pete

**Glenn Kennedy** · 07-03-2023, 05:30 AM

or, if you have updated from Excel 2016 to O365... delete ALL expected results and use:

=MAP(A2:A21,B2:B21,LAMBDA(a,b,IF(ABS((b-a)/a)<0.1,"Yes","No")))

It spills down automatically.

**darrenj1471** · 07-03-2023, 05:34 AM

You guys are absolute legends. If the values are in columns F and L how would that change things? (although its no hardship for me to move them to A and B I guess)

**HansDouwe** · 07-03-2023, 05:34 AM

Please try

Formula:

Please Login or Register  to view this content.

**HansDouwe** · 07-03-2023, 05:37 AM

If the values are in columns F and L how would that change things?

Please change A and B in F and L

**Pete_UK** · 07-03-2023, 05:37 AM

Originally Posted by darrenj1471

You guys are absolute legends. If the values are in columns F and L how would that change things? (although its no hardship for me to move them to A and B I guess)

Use F2 instead of A2 and L2 instead of B2 in the example formulae that have been suggested.

Hope this helps.

Pete

How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

LinkBack

Thread Tools

Rate This Thread

Display

How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Re: How to return a Yes or No to indicate if the value in one cell is plus or minus 10%

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Adding a MAX to a formula to prevent a return of a minus result

[SOLVED] When minus cells return value if error

[SOLVED] Lookup Date minus 1 day and return Values from the Matching Headings in 2 Worksheets

[SOLVED] Swits leading minus and put minus infront of numbers without macro

How to return a balnk cell if the result of an = is a minus

Return value of col. A based on max of col. B minus single cell

Return highest minus lowest value in a rng which is defined by location of a spesfic value

Bookmarks

Bookmarks

Posting Permissions