Mario has built a great nested IF formula for assigning points to soccer games. However, the formula is calculating 1 point for both teams for all unplayed games. In Episode 1041, I take a look at how to modify the original formula to calculate nothing until a score has been entered.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen basically we start out with massive amount of data.
How we're gonna analyze as well let's fire up a pivot table. You can solve this problem.
Hey welcome back to the MrExcel Netcast.
I'm Bill Jelen. Today's question sent in by Mario.
Mario is tracking football or soccer as we call it here in the United States.
Results in, it's a fairly complicated thing if you win the match you get three points if you lose the match you get zero points and if you tie, you get one point.
and he has the formula working out really well if C3 < G3 then you get the three points and then a Second nested if statement, checking to see if they lost if C3 < G3 then 0 otherwise the 1 point for at tie.
We point out that you could also do this checking for the tie second, if G3 is equal to C3 1 point otherwise 0.
That's all working fine, but here's mario Soccer, mario says: for the future games when I've copied this formula down it's giving one point to everybody because it's looking as if it's a 0 to 0 tie.
How can I prevent it from calculating 1 as a result for future games, and I'm going to introduce a function called ISBLANK.
ISBLANK. So we're going to Edit Mario's formula and =IF(ISBLANK) So C4 then Nothing. quote quote otherwise the rest of Mario's formula, and then at the very end, I press the end key and put a final parenthesis, and you see that that will now work if the score is filled in we get the exact same result, but if the score is not filled in for future games it will show up as blank. Now when we actually play that game and Let's do a 2-2 tie you see that then we will get the result.
So if you have a great IF formula working But it needs to not calculate until the values are filling ISBLANK a great way to go.
Hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen basically we start out with massive amount of data.
How we're gonna analyze as well let's fire up a pivot table. You can solve this problem.
Hey welcome back to the MrExcel Netcast.
I'm Bill Jelen. Today's question sent in by Mario.
Mario is tracking football or soccer as we call it here in the United States.
Results in, it's a fairly complicated thing if you win the match you get three points if you lose the match you get zero points and if you tie, you get one point.
and he has the formula working out really well if C3 < G3 then you get the three points and then a Second nested if statement, checking to see if they lost if C3 < G3 then 0 otherwise the 1 point for at tie.
We point out that you could also do this checking for the tie second, if G3 is equal to C3 1 point otherwise 0.
That's all working fine, but here's mario Soccer, mario says: for the future games when I've copied this formula down it's giving one point to everybody because it's looking as if it's a 0 to 0 tie.
How can I prevent it from calculating 1 as a result for future games, and I'm going to introduce a function called ISBLANK.
ISBLANK. So we're going to Edit Mario's formula and =IF(ISBLANK) So C4 then Nothing. quote quote otherwise the rest of Mario's formula, and then at the very end, I press the end key and put a final parenthesis, and you see that that will now work if the score is filled in we get the exact same result, but if the score is not filled in for future games it will show up as blank. Now when we actually play that game and Let's do a 2-2 tie you see that then we will get the result.
So if you have a great IF formula working But it needs to not calculate until the values are filling ISBLANK a great way to go.
Hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
