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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920