Bill Jelen: Hey, I’m Bill Jelen from Mr. Excel.com and I’ve got a cool excel tip for you today.
Mike: Hey this is Mike Gel Girvin in Excel is fun on YouTube and I have a different way to do that.
Bill Jelen: Hey, welcome back for another dual Excel pod cast from Mr. Excel we will be joined later my Mike Gel Girvin from Excel is fun. Today we have a bonus calculation, someone sent this in now, you want to pay at 10% bonus if your days are larger than ten thousand dollars we have a couples up here that hurdle the ten thousand and the percent of your bonus.
Let’s just put a little format here with your equal, open parenthesis, today’s sales, that’s B7 minus the hurdle. So I click on B1 and I’m going to press the F4 key to put dollar signs in and that locks that down so whatever we sell points that, times the percentage rate in B2 and again F4 to lock that down. So there you go 87 dollars and 67 bonuses that day.
Let’s double click to copy that down, and we are going to see that that works great when you are greater that ten thousand, but when you are under then thousand, oh we have a problem. You know hey, thank for showing up today but since you didn’t sell ten thousand, not only were going to pay you but you owe us for the pleasure working here. Now that’s never going to work.
So let’s come back and edit this format. We are going to use and if function. So equal if B7 is greater than or equal to ten thousand commas then, we do our calculation. Otherwise we’d come here and do commas right here at the end. So that’s where the negative amount will show up as zero, copy that down. All right there you go. That’s method one let’s send over to Mike and see what Mike comes up with it.
Mike: Thanks Mr. Excel, hey this formula right here this is greater than or equal to and then the value of true was the bonus, but it’s perfectly legitimate to do a slightly different logical construct.
So we’ll say equals if and instead of saying this, is this greater than equal, we’ll say is this less than ten thousand, and then lock that one with our F4 key. If that’s’ the case what do we want? We want a big fat zero. Otherwise, we want in parenthesis whatever this is, minus that so we get just the amount over the ten thousand, I’ll lock that, times our ten percent and lock that with the F4 key, close parenthesis. Ctrl + Enter double click and send it down.
So, really no difference between these and if you go on look at people’s spread sheets. You know, good proportion of people do this and it’s pretty much up to preference. All right I’m going to throw back to Mr. Excel because I think he has another way to solve this.
Bill Jelen: Okay, well that’s cool but let’s do something else, let’s go all the way back to episode 225 of the Podcast. We are taking advantage to something called bullion number facts. If you have the word true, the value true and multiply that times time a number. The true gets treated like a one, so one times the number is the number, but the value false times the number falls, because it’s treated like a zero and that clears things out. So here I hand that original formula that I used and I’m going to multiply this, multiply this times a bullion logic explanation. So I’m going to say if B7 is greater than or equal to dollar sign B, dollar sign one. All right, and so it takes that calculation which was either positive or negative and says hey! I’m going to multiply it with this true, false, and what we get is. If its true, then the number shows up, if its false, then we get a zero.
Very cool way to go. Mike, top that!
Mike: Bullion? That is totally cool. Hey, lets go over here. Let’s try something different here. How about I’m going to do basically the same formula you start off with. We’ll see, today’s sales, minus the hurdle and hit my F4 key, close parenthesis, times the rate and then F4 to lock it. Ctrl + Enter double click and send it down.
That will give us some negatives and some positives. How about this? Now watch, the active cell is the light colored cell and the whole range is highlighted, I’ll hit F2 to put in edit mode and here’s the max function max, open parenthesis, now it’ll take a bunch of arguments separated by commas, so I’ll put the commas, so I’ll put a comma zero. And what is it going to do? When it sees a negative, the max and the negative and the zero will be zero but when it sees a positive, it will take the max of a positive and zero will be the positive.
Now the whole range is highlighted, hold control and tap Enter to repopulate your edit formula. Hey, so the max now, I can’t take credit for that, because that is from page 182 in the learn Excel from Mr. Excel but I’m going to throw it back to Mr. Excel because I think he has one more method.
Bill Jelen: Max, oh that’s brilliant! Max, I love that trick, great trick. All right, last one, Vlook up. If we have a zero in sales, you got zero percent bonus, if you have ten thousand sales, you got a ten percent bonus for billing on the Vlook up table over here, and what I’m going to do is equal Vlook up, this value in this table, of course I want to look that down, so I press F4 and I want the second column, column two, and finally I want not comma false at the end, comma true, although we don’t have to put that in it’s the default. So what we’re going to get is either zero or zero point one all the way down, great way to go. Now if I’m going to go and edit this formula, times the value that we sold, minus the threshold, the hurdle up here, press F4, press Ctrl there to accept that value and put it in everyone in the selection and there is yet another way to go.
Hey, great question today, several different answers. I want to thank Mike for that max idea, excellent idea, you can use any of these, and all will work your choice. I want to thank you for stopping by, we’ll see you next time, for another net cast from Mr. Excel in Excel is fun.
Transcription by:
Scribe4you Transcription Services