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 Formula Tips from MrExcel (Laminated Card)
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1815.
Within plus or minus 1 of the target is a hit, otherwise a miss.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in a comment on YouTube for my episode 769.
Is there any way an if statement can be checked for data plus or minus 1 of a value?
So, if it's greater than one and less than minus 1, I will say hit and if not it should say miss.
Can you do this using the IF statement?
This is a great example of, there's so many different ways to solve this formula in Excel and what I initially wrote it.
I actually, tried to follow the suggestions of the comment here with equal IF and then immediately launched into the AND function where we test for both things.
So, the first test is the Goal minus Actual, less than or equal to one that's the first test and then the second test is Goal minus Actual greater than or equal to minus one.
If both of those are true, then we're within one and we say, Hit otherwise we say, Miss.
Now, this is backwards from what we have here.
They're saying if it's greater than one and less than minus one, that's a hit.
I think that's reversed, but if I'm wrong, if I don't understand the point here, then reverse the miss and the hit there.
Press [ Enter ] and double-click to shoot that down.
Now, the frustrating thing here is we have to do the A2 minus B2, twice.
I'm going to go into the next worksheet, one choice is to do the A2 minus B2 in an extra helper column, and that will shorten this formula over here.
So, equal if and either this is less than or equal to one or it's greater than or equal to minus one, if both of those are true, then hit otherwise, miss.
And so I answered these for these types of formulas in the comment at Youtube, I said well, wait wait we can simplify this greatly by using the absolute value function.
Absolute value, so if we look at this difference.
You know, there's going to be positive numbers, there's going to be negative numbers.
It's all over the board, but if we take the absolute value of that number.
So ABS, it takes the number and changes it to the distance from zero in a positive number.
So, if it was negative it turns positive.
This tells us, how far we were from the actual but it doesn't tell us, whether we were greater than or equal to.
This simplifies the IF statement, IF statement that it's equal if this is less than or equal to 1, then we were close, otherwise it was a miss.
All right! And finally, we can simplify that formula without the helper column equal if the absolute value of this minus this is less than or equal to 1 then hit, otherwise miss.
And copy this down.
All right! So, for different Excel formulas that achieve the same outcome if you like these formulas, check out my new laminated tip card.
Two pages lots of different formula tips and tricks right along the lines of this tinyurl.com/Excelfor F O R, we'll take out to the MrExcel's Excel store, where we have this tip card and seven other new tip cards charting vlookup, pivot tables, Excel 2010, Excel 2013 and even macros to VBA out there.
Hey, I want to thank your stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1815.
Within plus or minus 1 of the target is a hit, otherwise a miss.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in a comment on YouTube for my episode 769.
Is there any way an if statement can be checked for data plus or minus 1 of a value?
So, if it's greater than one and less than minus 1, I will say hit and if not it should say miss.
Can you do this using the IF statement?
This is a great example of, there's so many different ways to solve this formula in Excel and what I initially wrote it.
I actually, tried to follow the suggestions of the comment here with equal IF and then immediately launched into the AND function where we test for both things.
So, the first test is the Goal minus Actual, less than or equal to one that's the first test and then the second test is Goal minus Actual greater than or equal to minus one.
If both of those are true, then we're within one and we say, Hit otherwise we say, Miss.
Now, this is backwards from what we have here.
They're saying if it's greater than one and less than minus one, that's a hit.
I think that's reversed, but if I'm wrong, if I don't understand the point here, then reverse the miss and the hit there.
Press [ Enter ] and double-click to shoot that down.
Now, the frustrating thing here is we have to do the A2 minus B2, twice.
I'm going to go into the next worksheet, one choice is to do the A2 minus B2 in an extra helper column, and that will shorten this formula over here.
So, equal if and either this is less than or equal to one or it's greater than or equal to minus one, if both of those are true, then hit otherwise, miss.
And so I answered these for these types of formulas in the comment at Youtube, I said well, wait wait we can simplify this greatly by using the absolute value function.
Absolute value, so if we look at this difference.
You know, there's going to be positive numbers, there's going to be negative numbers.
It's all over the board, but if we take the absolute value of that number.
So ABS, it takes the number and changes it to the distance from zero in a positive number.
So, if it was negative it turns positive.
This tells us, how far we were from the actual but it doesn't tell us, whether we were greater than or equal to.
This simplifies the IF statement, IF statement that it's equal if this is less than or equal to 1, then we were close, otherwise it was a miss.
All right! And finally, we can simplify that formula without the helper column equal if the absolute value of this minus this is less than or equal to 1 then hit, otherwise miss.
And copy this down.
All right! So, for different Excel formulas that achieve the same outcome if you like these formulas, check out my new laminated tip card.
Two pages lots of different formula tips and tricks right along the lines of this tinyurl.com/Excelfor F O R, we'll take out to the MrExcel's Excel store, where we have this tip card and seven other new tip cards charting vlookup, pivot tables, Excel 2010, Excel 2013 and even macros to VBA out there.
Hey, I want to thank your stopping by, we'll see you next time for another netcast from MrExcel.
