Excel - Within +/- 1 is a Hit - Episode 1815

1,088 views · Published 25 October 2013 · 3:51 · Indexed 30 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Determine if a Value is Within Plus or Minus 1 of a Target.

Welcome back to the MrExcel podcast. In this episode, we will be discussing a common Excel problem - how to determine if a value is within plus or minus 1 of a target. This question was sent in by a viewer in the comments of one of our previous episodes, and it's a great example of the many different ways to solve a formula in Excel.

The initial solution I came up with was to use the IF statement, following the suggestion of the comment. We can use the AND function to test for both conditions - if the difference between the goal and actual values is less than or equal to 1, and if it is greater than or equal to -1. If both conditions are met, we can say it's a hit, otherwise it's a miss. However, this solution requires us to do the calculation twice, which can be frustrating.

To simplify this formula, we can use the ABS function to find the absolute value of the difference between the goal and actual values. This gives us the distance from zero, regardless of whether the number is positive or negative. We can then use this value in our IF statement - if it is less than or equal to 1, it's a hit, otherwise it's a miss. This eliminates the need for a helper column and makes the formula more efficient.

If you enjoy learning about different Excel formulas and tips, be sure to check out my new laminated tip card. It covers two pages of various formula tips and tricks, including the one we discussed in this episode. You can find it at tinyurl.com/ExcelforF-O-R. And don't forget to visit the MrExcel Excel store for even more tip cards on charting, VLOOKUP, pivot tables, and more. Thank you for watching, and we'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction and Sponsorship
(00:10) Episode 1815: Learning Excel
(00:20) Using IF statement for +/- 1 value
(00:30) Different ways to solve formula
(01:01) IF statement with AND function
(01:28) Simplifying formula with helper column
(01:59) Simplifying formula with absolute value
(02:49) Final simplified formula
(03:09) Laminated tip card for Excel formulas
(03:41) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Absolute value function
Data plus or minus 1
Episode 1815
Excel tip card
Formula tips and tricks
Goal minus Actual
Helper column
Hit or miss
IF statement in Excel
Learn Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153636/



It is Hit or Miss today at the MrExcel podcast, literally. The question is how to label a value as a hit if it falls within plus or minus 1 of a target. Bill shows four different formulas to solve the problem. To buy the Formula Tip Card mentioned in the episode, visit http://www.mrexcel.com/store/index.php?l=product_detail&p=274

More from this channel