Kristy Asks How to Get Percentages from Scores

Beginner

About this Tutorial

How to figure out a percentage score when our reporting numbers are a formatted in a particular way. In this case we have a score and then a slash and then the total possible score in a single cell. We go through each option of solution and try to derive a solution that is both useful in the exact situation and possibly useful for other problems too.

We find that the Query formula and other solutions are inflexible or time intensive. We want a solution that does actually solve the problem at hand, in a quick way, and also is easy to edit and flexible to solve other problems.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

ARRAYFORMULA() IF() INDEX() ISBLANK() LEFT() QUERY() RIGHT() SPLIT()

Video Transcript

<div>00:00 Hello, so here's a simple problem, or at least I thought it was a simple problem until I realized what was going on. 00:07 So over on I Love Google Sheets, it's a free Facebook group for anyone who likes Google Sheets or wants to get help, or even wants to help. 00:15 So Christy here is trying to get all of these scores. Okay, so here's a simple problem. Here in the middle you can see 44 divided by 50, 50 to 40, and get the percentage of them. 00:28 She wants to take this score and get a percentage.

And I saw this and I was like, of all correct in exactly this particular way, but it tends to be one hard to implement if you're using the query formula down here. 00:52 One of, somebody said that there, there's sort of pros and cons to all these solutions. So Ian here says, you know, left, right. 00:59 And, totally is okay if all of the numbers were exactly two digits. Here, Muhammad says, use a formula C one times 100. 01:13 I wonder if that actually works. We're going to test it out in this video. Just delete this slash 50.

Okay, like yeah, you could sort of get that first thing and you know, they're all 50, but, hmm. 01:25 Again, this sort of pros and cons not flexible. It'll work in this particular situation. But not others. Here's someone who is, what are they doing? 01:39 Removing the first mistake data so that you can later use the sheet for adding other test scores. Yeah, like you want to be able to say, okay, if I have this exact format later on, can I do it again and again and again? 01:51 Right? Do I, can I set up a sheet that I'm like just import these numbers?

Here, Google sheets expert G sheets, g sheets.com has an array formula plus. 02:01 If plus query and again, let's, let's test these out and I have my own possible solution that I want to try myself and I want to see what is the sort of balance? 02:13 What are the things we can do to just get the job done versus like, oh, if we have any, any kind of format like this again. 02:21 Can we change it, edit it or can we import it? There's, there's sort of this range of flexibility. So let's, let's create a sheet and that has exactly this and we'll try to solve it for Christie. 02:34 Okay.

So I have some scores, but I don't like how the sheet looks. So I'm going to try to just quickly make this look better. 02:41 Okay. So just in a click, I, I just wanted to change the background color a little bit. So now we have these scores and we want to get our percentages here, just like in Christie's case we have them in C in the column C and we want to get percentages in the D column. 02:55 Now look, I don't exactly know what's going on here.

It looks like that there is a space between 44 and the slash, then there's a slash and then a space and 50. 03:07 That might not be exactly the case all the time, but it does look pretty clear that that is the case. 03:16 So let's go through a couple of these problems or solutions to this problem and see if they work. So let's see if this left and right work, and it does look, you can see the 88 and we're going to, you know, we can absolutely sort of do that. 03:31 Right. But in the case, let's see if we don't have these spaces. Does it still work?

And it absolutely does. 03:40 Right. Because we are guessing that there are some character, it could be any number of characters between these two numbers and that these numbers are always going to be two digits. 03:54 Let's see if it's like one, somebody did really bad on the test. Let's see if this will solve it. And it does. 04:00 Right. Why does it do that? What is this? Left doing, we will, we can actually look here and see. You just do, yep. 04:09 It's just going to get the one. What if it's not out of 50. What if it's out of four? Right. 04:16 Totally fine. Right.

Because it's getting the left two and the right two and this right two is for. And this left two is one with, because it has a space. 04:27 If we don't have a space. Error , error , error . We get errors. If we have something like this, if we have, what if we have, and now it thinks it's a date. 04:36 Okay. Oh, we're going to have some problems with that later. All right. What if it's zero four? 83, right? Totally fine, because we have two characters on the left, and two characters on the right. 04:50 If we take out that zero, we have an error . So, sure.

This is a solution to this exact problem, and I would, I would guess to say, you know, there is one extra thing we can do, but in addition to this left right, it, is wrap it in an array formula , and do this c2, we have to do two colon c, and c2,  05:12 we have to do colon c as well. I would wrap it in an array formula , but you're gonna get all of these errors, right? 05:18 And now here's where actually a bigger problem happens.

If you end up doing this, and then go if, error inside, sorry, if error , and sort of saying, let's just get rid of all the errors. 05:32 We know this works, we saw it happen ten times, we'll keep doing it, and then you end up having some errors, and you're like, ah, I just don't want to see the errors. 05:40 You do if error . But now this one that actually has a different, set up in a different format is a complete blank. 05:47 So you will have to still do spot checks and stuff with this.

All right, so that's sort of problematic, right? 05:56 Again, it does solve this solution in the moment, but it doesn't actually solve, like, for flexibility. We can do a ray formula . 06:04 Fine, we're going to get some errors. We could do if error , then we're going to have a bigger error . And we're just going to have to go through this manually ourselves anyways. 06:12 So let's look at some other options. Muhammad here says use the formula C1 times 100 in D1 to calculate the percentage for each corresponding cell in C, column C. 06:23 Drag the formula down.

D1, now this seems like he didn't realize that there were that this is not a thing. 06:31 This is not like anything. But let's see. I mean, I just want to see what happens if that is the case. 06:37 And also he says C1, so I think he meant C2 here. Let's just, because C1 is just score times 100. 06:45 But let's see. We're just going to get a value error . And it tells us right here, a function multiply parameter one expects a number value, so we're not getting a number . 06:54 And you can see right away, this is not a number .

But 44 divided by 50 is a text cannot be coerced to our number . 07:01 So this is wrong, right? Well, we need the spaces. Let's go fix these up a little bit and get some more. 07:10 Right? So this is not a good solution, because it literally is a different format here. You know, if sort of this multiplication only could definitely work if there were like other columns here. 07:23 If this was 44 and 50, you could do. Equals a one divided by. B. No, not a one. I did the same mistake. 07:32 A two B two. And you get a percentage in the format that is percentage. Great, right?

But we don't have that format . 07:39 We don't have the luxury of that. Perhaps we could split this. You can definitely split this tech. C two. And that's sort of going towards what I was thinking of doing is doing. 07:53 Let's look at that split . Split the C two text by this and we get 44 and 50, right? And then now we can do equals. 08:07 E two divided by F two and we get our percentage, but like we want to do that all in one. 08:13 One cell, right? We don't want extra cells here. We want to do it right here.

So instead of this split or in addition to this split , let's do index . 08:24 We can do index to say we just. Want of this split the first thing, which will be the first row and the first column, which will be 44. 08:32 See, we have the number 44 shows up here. Do exactly the same thing. Take exactly the same index . Okay.

Instead of one at the end, the column one, we want column two, and now we have two splits with the index saying, give me the first thing and then giving the second thing. 08:56 And in this case, if we just do index and split and this index one on the first one divided by the index of the second one, now it doesn't matter if there's that space. 09:07 Or not. So Google Sheets is sort of knows enough to say, okay, this is 44 and a space. And then the other one is going to be a space and 50. 09:17 You could see it here.

We could even test it out without doing all of the entire function itself, right? You just do equal split . 09:24 You just did it split . And then you see the number like, oh, I can use the index . An index , which is a really silly sort of you don't think there's any use for it until you're like, oh, I can index this split. 09:36 And this works out perfectly. This sort of formula combination of index and split is just like super useful.

And for some people, including myself, it's way. 09:47 Easier to do something like this to say, okay, I just want to split this, but by this slash, and then I want to take the first one and divide it by the second one. 09:55 Much easier than the other query options that are also here, but let's go and look and discover some other ones. 10:02 Because I think this is the most flexible. And I think this is sort of the answer here. And we can also absolutely. 10:07 Array formula this. Let's do that now and see what we get.

We're just going to change C2 to C, and we're going to get some issues, but we're going to solve those along the way. 10:20 Should be able to. So we should be able to use the array formula . With index , if we just take out the row here, so we have our index split , and then we can usually do. 10:35 Let's do index split again. But this time we want the two. And we want to divide. And so we get this now. 10:48 Numbers above one here.

We now have array formula wrapped around index split divided by index split , but just took out the row. 10:58 Because if we add the row, what was happening is it was saying of this array formula . Give me the first row. 11:05 But we want as it goes down. We just want that row so we can see. This is not correct, right? 11:11 That's.88 for the whole. All of them. We don't want that. We want the actual number in the score each time. 11:18 So we have this array formula . And in this case, it might look complicated.

It might look much simpler to do a query and then figure out all this mishmash. 11:29 Inside the query . But let's see, I think for myself piecing this together is sort of more simpler and step by step. 11:36 This is all of the steps that we need to do. If we were to do this manually, right, we need to take the 44 and the 50. 11:43 We know visually we can split this between the slash. Take the first one divided by this. Second one and then do that for each row. 11:51 This is in my head how I would solve this problem manually and now this formula is doing it.

But let's look at query and see if we have some other, another option. 12:02 Because we do, there's always different ways to do this. Here, Roberto said delete this slash 50. Sure. That's okay if you want to delete the slash 50 from all of them. 12:12 What if we just want to import them, get the percentage and be done? So this one explains, I think, the query formula . 12:25 But doesn't actually give me something to copy and paste, right? And then here. Google Sheets Expert mentions this equals array and then has the query in here. 12:35 Which I think does exactly the same thing.

Let's check it out. So we see an array formula , we see if C2 is nothing. 12:47 I don't know why. Query , split , and query and they're gonna select column1, slash, two, label , column1, slash, let's see if this works. 12:57 It works, right? But like, can you actually figure out what this is doing? Oh, right here, this split is the same split . 13:06 C2 and it's just an array formula. We can take off this array formula, let's see what happens. We just need to change C2 colon C to C2. 13:17 We need to take C2 colon C, change it to C2, and we need to take out the last array.

So let's look commas here if C2 is blank, it'll do nothing. 13:35 So if we take this, and we go down all the way to the bottom. Whoops. Oh, I think I just, there, gotta. 13:47 If we go all the way to the bottom to a blank row, there we go. We got nothing, right? Because of that if. 13:52 So let's take off that if, cause that's a simple, same thing, if blank, or if it's nothing. And let's look at this query , right? 14:01 And now this query is doing exactly the same work that our index and divide is doing. And sort of.

Just a more exploit explanatory way with query , we have to sort of explain it select like in words and in a particular syntax , right? 14:22 Select column one slash column two labels column one slash column two. This is sort of a completely different. Different syntax , right? 14:32 Then we're normally using in our formulas. So again, it could be possible if you know all the possibilities of the query formula and sort of, sort of SQL kind of SQL sort of thing, right? 14:44 And it uses exactly the same thing we've used, right?

We're splitting C2 and, but this I feel like it's much easier to read. 14:51 If we take off all of this stuff, we're just taking the index of the first thing of the split and the second thing of the split , come dividing them. 15:01 But really cool way to see different ways to use this, right? And now I'll reply to Christie's post and give her this another another way where we have index and th- split here with the right formula . 15:13 Alright, thanks for watching. I hope you enjoyed this sort of breakdown of this seemingly simple issue, right?

And we really wanted to find the right flexible kind of formula versus just getting it done again. 15:29 We can sort of take this, these numbers. 44, 50, or remove slash 50 from all this. We can do a number of different ways, but this formula gives us the solution right away. 15:42 And it's flexible for other scores like this.</div>