I was recently reminded of a basic programming challenge called FizzBuzz. Here is my Excel solution.
The Challenge
Create a list of the numbers from 1 to 100 inclusive.
If a number is evenly divisible by 3 display the text Fizz.
If a number is evenly divisible by 5 display the text Buzz.
If a number is evenly divisible by 3 and 5 display the text FizzBuzz.
If the number is not evenly divisible by 3 or 5 display the number.
The Excel Solution
The list of 100 numbers is created using.
=SEQUENCE(100)My formula to display the correct text is.
=LET(n,A1#,f,IF(MOD(n,3),"","Fizz"),b,IF(MOD(n,5),"","Buzz"),IF(f&b="",n,f&b))The image below shows the top of the sheet.
The MOD function returns the remainder after dividing one number by another.
When you use the MOD result as the logical test in an IF function it works like this.
If MOD returns zero (it means it is evenly divisible) then the result is treated as FALSE.
All other MOD results return TRUE because they are a number and all non-zero numbers are treated as TRUE.
If both f and b are blank, then the number is displayed. All other results show the combined text of f&b.
There is no doubt a simpler way to display this but this is my best attempt.
Why use a straightforward and easy to understand formula like you did when you can use a complex hard-to-follow one using the same number of characters instead?😁
=LET(b,BYROW(IF(MOD(A1#,{3,5})=0,{“Fizz”,”Buzz”},””),CONCAT),IF(LEN(b),b,A1#))
Hi Rick
I must admit I was hoping you would show an alternative.
Great use of the array syntax and CONCAT.
You could save a few characters as below by getting rid of the =0 and switching the arguments.
=LET(b,BYROW(IF(MOD(A1#,{3,5}),””,{“Fizz”,”Buzz”}),CONCAT),IF(LEN(b),b,A1#))
Funny you should mention that. I was out with a friend this afternoon and blurted out (loud) out of nowhere, “Damn, I didn’t need the equal zero”. He looked at me, so I had to explain what I meant (he’s not an Excel person, so I had so simplify). I would have posted the correction here then, but I find typing out almost anything on my smartphone to be an excruciating chore, so I figured I wait till later… you beat me to it.
I just noticed the clever way you used the IF as the first argument for BYROW – removing the need for a LAMBDA as the second argument.