No Links were listed yet. Go ahead and share!
Hey, welcome back to the Mr. Excel net cast. I’m Bill Jelen. Amazing! We start out with massive amounts of data and how we’re going to analyze this, well—and see if we can solve this problem?
Hey, welcome back to the Mr. Excel net cast, I’m Bill Jelen. And today’s question sent in by Mario. Mario is tracking a football or soccer as we call it here in the United States results and 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 is less that G3 then you get the three points and then a second nested statement checking to see if they lost. If C3 is less than G3, than zero, otherwise the one point for a tie will play out. You could also do this for checking for the ties.
Second, if G3 is equal to C3, one point, otherwise zero. That’s all working fine but here’s Mario’s question where as for the future games where I’ve copied this formula down, it’s giving one point to everybody because it’s looking as if it as zero to zero tie. How can I prevent it from calculating one as a result for future games and I’m learning to introduce a function called ISBLANK.
So, we’re going to edit Mario’s—and say, equal 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 one now work. If the score all in, we get the exact same result but if the score is not filled in for future games, it will show up ISBLANK. Now, when we actually play that game and let’s do a two-two 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 filled in, ISBLANK are great way to go. Hey, I want to thank you for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services