You have a formula doing a calculation. If both input cells are 0, the formula needs to use an override. Bill and Mike offer alternatives using AND, CHOOSE, and more in this Dueling Excel podcast.
Transcript of the video:
Hey, welcome back. It's time for another dueling excel podcast. I'm Bill Jelen from MrExcel.
I'll be joined by Mike Garvin from Excel Is Fun. This is our episode 151- IF OR ?
All right, a kind of interesting one today.
I have a feeling this could do a lot of back and forth from YouTube trying to take D2 + E2 and multiply that times 1.2 But if both D2 and E2 are 0 then we need to use the default in in F alright.
So here is the original formula that newman123 had D2 + E2*1.2 and I I have two different ways to solve this. I'm sure that Mike probably has a dozen ways to solve this.
But what I'm going to do is start with an =IF and then inside the IF use the AND function.
AND is this great versatile function. It says. Hey, we're going to make sure that all of these things are True.
So check if D2 is True, if E2 is True, if all of that is True, we could keep putting more in there but we won't have to then use the default in F2. Otherwise use the same formula we had.
But I'm going to change from D2 + E2 to use a SUM function. That way later on if we come up with additional like Num3 Num4 Num5 and put it between D and E that will automatically expand multiply that times 1.2 closing parenthesis, and we're good to go.
I like this one because it actually makes sense to me.
Someone, a co-worker can come up and see this and understand it.
We're making sure that both of these things are True. If that's True then we use the default in F2 otherwise, we do the regular calculation. I try to come up with something a little bit off the wall, not saying Mike that you always come up with something off the wall, but I figured you must have some other way to do this. So same formula.
SUM of D2 times E2 or D2 to E2*1.2 All right we get that.
Now if it happens that both of these are 0 that number is going to be 0.
So I'm then going to take F2* this boolean expression D2 = 0 E2 = 0. If both of those are True then we get F2*1*1 and we get F2. If either of them are False F2 *0* whatever anything times 0 is 0.
So in this case either this part of the formula the first part of the formula is going to be non 0 or the second part of the formula this can be non 0.
And we can just add them together every time we don't even need an IF statement.
All right Mike let's see what you have.
Thanks, MrExcel. Hey, you know. I love both of the formulas you did.
This one's great because as you said hey, the logic of how this formula works is right there. right True True then put this, otherwise that. Ah.
And this boolean bit of magic that you did here, I love this and I like this little bit the most because how smart, always going to get a number except for when it sees two zeros.
Then SUM is going to add to get zero. So I'm actually going to steal that SUM part of your formula Ctrl+Enter double click and send it down, and I'm going to notice hey, there's a bunch of numbers except for zero.
When it sees two zeros well guess what, the IF function can interpret any nonzero number as True and zero as False, so I'm just going to slap the SUM right into the logical test argument.
Hey, if it sees any number besides zero what do I want? Well I need to add these And I like MrExcels idea of using the SUM instead of + + because if you insert any columns here this thing will update. Will multiply that by 1.2 Otherwise we need the default value that means when it sees the SUM total of 0 Control+Enter Double click and send it down. Hey some other ways to do this. There are a bunch of other ways.
Here's a crazy way, and I'm going to use the same AND that MrExcel did. Hey is this =0 AND is the second number =0 Hey that's a logical function.
So ofcourse it delivers True or False. It only delivers to True when it sees the two zeros.
Now normally if we convert logical values to ones and zeroes we do any math operation like add 0 or double negative but check this out and do some crazy. I'm going to add 1. Okay, wait a second that'll make False 1 and True 2.
And guess what I'm going to use that in the lookup function.
CHOOSE Now CHOOSE is a weird lookup function because it needs the lookup values actually in index number like 1 2 3 4 5 and then you just put the things as you want right into the lookup function CHOOSE. So value 1 well, it's going to be that SUM times 1.2 Hey value 2, its going to be the default value.
Now the CHOOSE is awesome because you can put anything into these values.
Text Numbers Formulas whatever you want.
Close parentheses.
Control+Enter Double click and send it down.
Hey, that's a silly one there and probably the best one of all of these is that = with the AND function.
All right I'm going to throw it back to MrExcel.
Oh my God its beautiful CHOOSE. I never saw that one coming. That is beautiful.
Well I want to thank everyone for stopping by.
See you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
I'll be joined by Mike Garvin from Excel Is Fun. This is our episode 151- IF OR ?
All right, a kind of interesting one today.
I have a feeling this could do a lot of back and forth from YouTube trying to take D2 + E2 and multiply that times 1.2 But if both D2 and E2 are 0 then we need to use the default in in F alright.
So here is the original formula that newman123 had D2 + E2*1.2 and I I have two different ways to solve this. I'm sure that Mike probably has a dozen ways to solve this.
But what I'm going to do is start with an =IF and then inside the IF use the AND function.
AND is this great versatile function. It says. Hey, we're going to make sure that all of these things are True.
So check if D2 is True, if E2 is True, if all of that is True, we could keep putting more in there but we won't have to then use the default in F2. Otherwise use the same formula we had.
But I'm going to change from D2 + E2 to use a SUM function. That way later on if we come up with additional like Num3 Num4 Num5 and put it between D and E that will automatically expand multiply that times 1.2 closing parenthesis, and we're good to go.
I like this one because it actually makes sense to me.
Someone, a co-worker can come up and see this and understand it.
We're making sure that both of these things are True. If that's True then we use the default in F2 otherwise, we do the regular calculation. I try to come up with something a little bit off the wall, not saying Mike that you always come up with something off the wall, but I figured you must have some other way to do this. So same formula.
SUM of D2 times E2 or D2 to E2*1.2 All right we get that.
Now if it happens that both of these are 0 that number is going to be 0.
So I'm then going to take F2* this boolean expression D2 = 0 E2 = 0. If both of those are True then we get F2*1*1 and we get F2. If either of them are False F2 *0* whatever anything times 0 is 0.
So in this case either this part of the formula the first part of the formula is going to be non 0 or the second part of the formula this can be non 0.
And we can just add them together every time we don't even need an IF statement.
All right Mike let's see what you have.
Thanks, MrExcel. Hey, you know. I love both of the formulas you did.
This one's great because as you said hey, the logic of how this formula works is right there. right True True then put this, otherwise that. Ah.
And this boolean bit of magic that you did here, I love this and I like this little bit the most because how smart, always going to get a number except for when it sees two zeros.
Then SUM is going to add to get zero. So I'm actually going to steal that SUM part of your formula Ctrl+Enter double click and send it down, and I'm going to notice hey, there's a bunch of numbers except for zero.
When it sees two zeros well guess what, the IF function can interpret any nonzero number as True and zero as False, so I'm just going to slap the SUM right into the logical test argument.
Hey, if it sees any number besides zero what do I want? Well I need to add these And I like MrExcels idea of using the SUM instead of + + because if you insert any columns here this thing will update. Will multiply that by 1.2 Otherwise we need the default value that means when it sees the SUM total of 0 Control+Enter Double click and send it down. Hey some other ways to do this. There are a bunch of other ways.
Here's a crazy way, and I'm going to use the same AND that MrExcel did. Hey is this =0 AND is the second number =0 Hey that's a logical function.
So ofcourse it delivers True or False. It only delivers to True when it sees the two zeros.
Now normally if we convert logical values to ones and zeroes we do any math operation like add 0 or double negative but check this out and do some crazy. I'm going to add 1. Okay, wait a second that'll make False 1 and True 2.
And guess what I'm going to use that in the lookup function.
CHOOSE Now CHOOSE is a weird lookup function because it needs the lookup values actually in index number like 1 2 3 4 5 and then you just put the things as you want right into the lookup function CHOOSE. So value 1 well, it's going to be that SUM times 1.2 Hey value 2, its going to be the default value.
Now the CHOOSE is awesome because you can put anything into these values.
Text Numbers Formulas whatever you want.
Close parentheses.
Control+Enter Double click and send it down.
Hey, that's a silly one there and probably the best one of all of these is that = with the AND function.
All right I'm going to throw it back to MrExcel.
Oh my God its beautiful CHOOSE. I never saw that one coming. That is beautiful.
Well I want to thank everyone for stopping by.
See you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
