 Maybe you've come across the game FizzBuzz before. It's a really simple kind of kids game for checking divisions You count the numbers off one two three four five and if it's a visible by three you say fuzz fizz If it's visible by five you say buzz if it's a visible by both you say a fizz buzz So the sequence is one two fizz four buzz fizz seven eight fizz buzz eleven fizz thirteen fourteen There's buzz that's the first 15 terms and you can continue on if you want to test your division to the limit and this sometimes gets asked as like an interview question for Are you any good at programming? Can you solve this problem? Can you get a computer to spit this sequence out? It's almost a trivial task, but it's kind of testing How you go about problem solving? What do you do? How do you set it up? And how do you check your work at the end? So the very simple way of doing it in excel is to simply do one two Let's drag that down to 15 and then we're going to test each of these in sequence So i'm going to start with an if and if I need to check the divisor the divisors of this and see the remainder is zero So i'm going to use the mod function I'm so i'm going to test cell c1 and divide it by three times five That's to make sure that we do 15 First we'll play about with it and show you why we might do that check that it's zero and if it's true we type in If it's false we're going to test it again, so if mod C1 well if just three if that's true we're going to say fizz Finally we'll check oops It's half of the wrong time mod of that and five Equals zero let's make sure that's correct Buzz and then C1 so if all else fails we'll just pick that number out just check this here we go I've you know forgot to put that in that should be equal to zero Yeah, that'll be fine And it's spat out the right number So there you go there you get to the correct answer there's your formula But this test is really not about getting the right answer. It's how you go about it. How do you structure it? If you are writing it up in computer code, how do you comment it if you're doing a data exercise in excel How do you format it for your end user? How do you give your user control over the sheet that you present them? So you might do some quite complex data analysis at some point as part of an interview in a spreadsheet and How do you spit that out to you? The manager who's asked for it. That's what the test is actually about Not getting the right answer doesn't work. So one quick thing we're going to do I'm going to test. Is this the correct answer? Well, I know this is the sequence I need to Produce so I'm going to test are these equal to my output. What's true? Is it true in all cases? Yeah, so let's just put an and at the bottom fill that Bolder and highlighted just so I can see it. So if I'd left in that mistake earlier You see it doesn't quite work out. It just fizzes fizzes fizzes everything. So it's false So I've clearly messed up my code there. I need to do zero If I did it three Then five then 15 here if I did these in the wrong order Also, it doesn't work, but I've got this big false here telling me that it doesn't work Okay, so now let's Do it in a bit more of a fancy way It's a slightly more complicated way But this is about structuring your output to have a lot of control over it and it's a little bit more Kind of computationally programatically elegant, I suppose. So we are going to Label some things up. So I want a divisor B And I'm going to a word b word because we've got Kind of two things here a and b. We've got three and we've got five and we've got fizz and buzz so three five fizz buzz Uh, and tell you what we're going to put out a length. How many Do we want to spit out how many sequences? So this Uh, is going to be the input that maybe a user gets so I'm going to come up to formulas Create from selection All the names are in the left column. Okay that now if I type a device I'm going to get three if I do b word I get buzz So these are going to be used Now one thing we can also do here is we can do sequence So if I do sequence The length here, I'm going to get the numbers one to a hundred. So we're going to start Not necessarily with there, but I'm going to do A new function called let this allows you to label things within a function and then reuse it um So I've already got some named ranges here. So this is another instance of doing this So name one I can call this x and x is a sequence from one to a hundred And usually the final thing in let will be well, what is the function? What that you're going to calculate so I've Created a variable called x x is now a sequence from one to a hundred. I'm just going to spit that out. Okay, great That's so far so good. But what I want to do is generate a couple of new tests. So a Well, I'm going to use that if statement. So I'm going to do at least the logical statement for it. So if mod uh of x divided by a deviser There we go, that's number a then I'm going to make a new one called b mod number x B's deviser There we go Now we can talk about the function. I'm just going to put in x and it'll still spit out one to a hundred. So clearly that's still working So uh, also it's worth pointing out that uh, this still says false. We haven't reached the right answer yet So what I'm going to do Is be in those if statements. So if Well a times b Is my logical test. So um Oh, actually I'm going to change this to equals zero and equals zero So those will spit out true and false True and false are also represented as one and zero in excel. So if you do a times b If this is true and this is true ill Interpret is true. So that's a way you can do it and statement. You can use the and function But that doesn't play well with spill formulas and I don't know whether it's by accident or design But this is the hack around it. You do a times b for and a plus b for or And so on So if that's true, I want to put out a word uh word and b word concatenate close together And if it's false, well if a a's word If that's false I want to check if b uh, then b's word And of all of that fails x then clear off a clear off all the brackets make sure I've finished all of that And there alone behold in a single line. We've got as many out as possible We've checked that it's true because we're checking it against at least the first 15 terms against a known set of data So we know it's correct. We know it's good to go up to at least 15. Um, oh actually That's it sequence, let's put the length back in There we go. Now if I change that to just 15 it stops Here I can push out to 500 and the computer will just Go forever. I can change the divisors. Maybe I want to change to two and five 7 11 there we go. It's a bit more sparse I might want to change from first to foo And bar but I've given this control over to whoever's going to play with it later Now this is obviously this is just a test This is kind of a little quiz to see can you do this? But if you're kind of being interviewed for programming or a data analysis job This is the kind of thing that probably looking for more than just getting the right answer Are you going to be testing your validity? Check that it's correct. Are you going to be handing a little bit of control To your end user? Yeah, and are you going to be putting your variables in a well-named format? So we don't want to just call this a b c d or instance or i j k l We want to give them descriptive names almost that it doesn't really need Almost doesn't we need comments to tell you about it So that's how you actually want to approach this sort of problem Give you plenty of flexibility Name your variables and test it