Count First Digit of Cell
Learn how to count the first digit of numbers in Google Sheets using Benford's Law to analyze data for potential fraud. This tutorial covers the necessary formulas and techniques to extract and count first digits effectively.
So, you want to figure out what is the first digit and count them up. Maybe one or the other thing you want to do, but I want to know why you're doing it. I'm doing this because I'm looking for fraud. Essentially, Benford's law says that out of any data set, any large data set that spans multiple orders of magnitude, the first digit is more likely to be lower than higher. Meaning like somewhat around 30% of all of these numbers are going to start with the number one. It's just like a thing with numbers and this Benford's law says that but I want to I want to see it in person. So what I'm going to do is I have the population world population 2026 right here listed every single country and I want to get the first digit. So how I get the first digit is equals left and then the string is
wherever cell has the information I want. So I'm going to reference this C5. I'm going to hit comma and all I'm going to do is the number one. That means it's getting one character length, but it's starting at the left of the number . So even if it's a 100 or 200 or 300, it's getting the first number . If it's 10 billion, it's getting the first number , number one. And so I'm going to hit enter there. And I'm going to copy and paste this all the way down. But I still don't know what the actual number is. So I'm going to count them up. So here in B2, I have one above it. So, I'm going to do equals count if and my range is going to be a column, comma, and I'm going to select the one in B1 as the reference. I could put the number one
here, but I want to have this as a reference . And there's 66. Now, if I copy and paste, I'll get zero, but that's not zero. We need to do something. We need to add a dollar sign in front of this num uh letter. And then we can copy and paste all the way over and it gets numbers. And you can right away see there are bigger numbers here 1 through 5 than 6 through 9. But let's see the exact number . I want to see the percentage equals this B2 divided by the sum of B2. Got to add a parenthesis there. Sum of B2 to J2. And again, if I just end parenthesis, hit enter, I'm not going to get the right answer. This is very weird that this all adds up to
134%. I need to add dollar signs in front of the B, the two, the J, the two, and that means that that's going to hold that range no matter what I do to copy and paste. However, the first number or the first reference C2, see that's changed. B2 C2, this is changing. So now I can copy paste this all the way across and I get each individual percentage of this count. Isn't that cool? Yes. 28.2%. That's very close to 30%. And you can see right here 1 through 4 is 67% of the numbers 6 through nine 20%. Wow. Really, really cool thing in Bford's law. And you'll learn how to count the first digit. You're watching Better Sheets
here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how-tos, get more out of your Google Sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is going to be your next Google Sheet.