"I was doing a Power Excel Seminar in Miami when I kept typing zero point zero two (0.02) in a formula. Of course, 0.02 is 2%. Someone asked me why I didn't cut the typing in half with 2%. Wow! Silly me... this clearly works!"
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
*Kindle is Coming to 'The MrExcel Excel Store'! Stay Tuned!*
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
*Kindle is Coming to 'The MrExcel Excel Store'! Stay Tuned!*
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel by MrExcel, Episode 1486 – Zero Point Zero Two Hey, everyone. I'm glad to be back from Miami.
Have a funny story, I was down there is doing a seminar for CFO magazine, one of their conferences, and I'm going through the POWER EXCEL seminar, which you know I've done a lot of times.
I have a lot of this just, you know, flat-out memorized.
We're talking about, if statements in the formula that I need here is,=IF(AND(A2>20000,B2>0.5),0.02*A2,0), equal if and sales is greater than 20,000, comma GP percent greater than 0.5.
If all that is true, then 0.02 times A2, otherwise 0, right.
And someone says, “Hey, what are you doing with the 0.02?” I said, “Well, that's 2%.” He says, “Yes, you're wasting a lot of space.” The first guy says, “You don't need the leading zero.” And the other guy says, “No, no, no, wait.
You don't need any of that.
Just put in 2%.” I’m like, what?
That works?
=IF(AND(A2>20000,B2>0.5),2%*A2,0).
And sure enough, that does work.
And look, I started wrong between there.
Hang on.
And then we'll copy this down, and so we get the bonus only on the rows that have both conditions, all right.
So if you've never seen AND before, AND is a great way to deal with this, but I was kind of like, “Oh, that's cool.
Hey, that saves me some key strokes.” And everyone in the room was like, well of course, you can do that.
Here, I've been typing 0.02 all this time, so you're probably looking at me and saying, well of course, you can.
You can just type 2% in your formula.
Never knew that, so I'm passing it on.
I learned something new down in Miami this week.
There you have it.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast with MrExcel.
Learn Excel by MrExcel, Episode 1486 – Zero Point Zero Two Hey, everyone. I'm glad to be back from Miami.
Have a funny story, I was down there is doing a seminar for CFO magazine, one of their conferences, and I'm going through the POWER EXCEL seminar, which you know I've done a lot of times.
I have a lot of this just, you know, flat-out memorized.
We're talking about, if statements in the formula that I need here is,=IF(AND(A2>20000,B2>0.5),0.02*A2,0), equal if and sales is greater than 20,000, comma GP percent greater than 0.5.
If all that is true, then 0.02 times A2, otherwise 0, right.
And someone says, “Hey, what are you doing with the 0.02?” I said, “Well, that's 2%.” He says, “Yes, you're wasting a lot of space.” The first guy says, “You don't need the leading zero.” And the other guy says, “No, no, no, wait.
You don't need any of that.
Just put in 2%.” I’m like, what?
That works?
=IF(AND(A2>20000,B2>0.5),2%*A2,0).
And sure enough, that does work.
And look, I started wrong between there.
Hang on.
And then we'll copy this down, and so we get the bonus only on the rows that have both conditions, all right.
So if you've never seen AND before, AND is a great way to deal with this, but I was kind of like, “Oh, that's cool.
Hey, that saves me some key strokes.” And everyone in the room was like, well of course, you can do that.
Here, I've been typing 0.02 all this time, so you're probably looking at me and saying, well of course, you can.
You can just type 2% in your formula.
Never knew that, so I'm passing it on.
I learned something new down in Miami this week.
There you have it.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast with MrExcel.
