00:00 Hello welcome. So in this video, we have member Nicole who asks this question about her savings sheet. She has a savings sheet.
00:09 She has a goal. She wants to keep track of how much she saving. And then she has this cool thermometer that she wants to fill up.
00:20 And I really like how this has sort of laid out where underneath there's an image here and it's hold out.
00:28 Like the center of the image is transparent and underneath are here. 30 gray cells going from row 39 to row 10 here.
00:42 And these gray cells should change based on how much is saved. So what she wants to do is actually hide the gray cell.
00:55 So the gray cells are actually, what's not wrong, but the wrong color she wants. If it is not to that savings, if it's less than a hundred percent, she wants to show this blue color.
01:07 And if there are some savings, meaning above 0%, why she wants it to be red. So it'll fill up red as it goes.
01:15 And you'll see visually up here. And there's 30 cells, which is interesting because it's a thousand dollars, right? So few things here, one, this allows us to always have 30 cells, no matter how much your savings is.
01:34 So if you want to do something similar and you want to save 10,000 or a hundred the solution here will be useful to you because you just have to spill in what your savings goal is.
01:46 We're going to use a percentage and have these 30 cells, many times whenever I've seen these kinds of savings goals, we'll use like a progress bar sparkline there might be like, you might fill in some cells based on like tens or hundreds of dollars.
02:02 Like you'll sort of take a simple path where you'll say, okay, I'm going to have these 10 cells and each one will be 10% and I'll just fill this in manually.
02:12 Right? That's sort of the regular way you try to solve this kind of thing. And I really like this because I think it's going to be flexible for anyone watching this who wants to do something similar, either a savings goal or work towards a goal.
02:28 And you want to keep this like number, right? This is 30 cells based on any percentage. All right, let's see how I did it.
02:36 So actually I'll show you what my answer. And I think it's actually a unique solution. I haven't seen anyone else.
02:44 Anyone else do this kind of thing again, you're going to look at, you're going to find solutions, which are like, look at like 10, 20, 30 simple percentages, and you'll be doing it manually.
02:55 But in this case, let me show you what I did. I have here, this thermometer, and we have $240 saved.
03:06 This is a S some of <inaudible> down here. So we didn't have to do that. But if we let's say we add another hundred, another 200 should be able to see the student.
03:20 Okay. 100. And there it goes, it goes up and up and up. And if we put a thousand and we get an error, but that will take care of that later.
03:31 But as we go up, we get up and up and up. So let me explain sort of, I'm going to go backwards a little bit, and then I'm going to go forward and tell you how I built this up, but let's go backwards.
03:46 First. I'm doing conditional formatting on the cells that are underneath the thermometer and what that conditional formatting is. Actually, we can even click here.
03:55 I will go here and show you what this conditional formatting is going to go up to format conditional formatting there.
04:09 So what we're doing on each of these 30 cells is saying if this cell in the K column, so we're, I'm using another column, but you can do this also in a sheet or something.
04:20 I just did it here. So you can see side by side, what's going on. So in the D column, we do conditional format and say, okay, over in the K column, if the corresponding row, meaning cake 10 in this case, and it'll be K, like 30 for row 30 all the way down.
04:37 If it's one, then we're going to have red. We're going to fill in that saved. If it's zero, we're going to have this blue, which is not saved yet where our goal we need to do now, how do we get the one in the zero?
04:54 This is the crazy part. What I did is I, I built two repeat functions, RET R E P T is a formula function that allows you to repeat a number a certain amount of times.
05:08 And you can set that number. You can have five times, 10 times, 30 times, 50 times, thousand times. And so I built two repeat formulas based on the percentage of saved.
05:18 And I said, basically, round two sorry. I, I said, take the percentage multiplied by 30 because we have 30 S we need 30 cells worth.
05:28 And then brown that, and then repeat either zero or one, repeat zero. If it's the percentage of yet to be attained, if it's percentage that has been saved, we want to multiply by 30 and repeat the number one.
05:43 Now what you get is a string and I'll show you, I'll build this up piece by piece. You'll get a string of zeros and ones, but like, how do you then like correspond those to 30 different cells?
05:56 I actually added a comma in between each one. And then I took the transpose formula and I said, I don't want this going to the right.
06:04 Sorry. I skipped one thing, split it. Then you add a comma. I added a comment between each one. So it's actually not zero feeding.
06:11 It's zero comma repeating and one comma repeating. Then I split. So then I got it across 30 cells. And then I used the transpose formula to flip it from going horizontally to vertically.
06:24 And then when you have 30 cells that essentially are either zero or one, you can then map the court, conditional formatting.
06:33 All right, I'm going to, if, if none of that made sense, I'm going to build it up piece by piece and you'll see how it works.
06:40 This is not necessarily the only solution. There are a couple of other solutions probably, but I thought this used the chance post formula in a pretty unique way.
06:49 It uses repeat, I think in a pretty unique way where we had to use a comma and split as well.
06:55 Oh. And a concatenate. We had to concatenate the two repeat functions together. So we got a lot of stuff going on and I'm going to build this up piece by piece as we go, all right, we're going to work in the M column.
07:08 And though, even though we have the answer right here on the K column, we'll know if we get the answer.
07:11 Correct. All right, first let's do repeat, let let's. Rept now, what is the text we want to repeat? It's going to be zero or one.
07:21 We'll we'll combine two separate formulas, but we'll just going to do the zero for now. And how many times do we want to repeat it?
07:28 We know out of 30. So we're going to, whatever we're going to do. We're going to multiply by 30. And what we really want to do is like the left over, right?
07:37 So out of this, now it's 6,640 out of 1000. So we're going to write B four before divided by B3.
07:52 And let's look at what that is actually, before we put the repeat, let's see what that looks like. So it's before divided by B3 is 0.6, four, right?
08:02 That's 64%. So what? 64% of 30. We can put this in parentheses times 30. We get 19.2. Great. So we want around that.
08:19 You want to, around the times thirties, we need to add some more percentages. All right. And now we want 19 cells, right?
08:27 19 zeros. All right. My mistake. That's one, but 64% is the one. Okay. If we want to get the zero, we actually need to do one minus this.
08:42 So we do one minus this percentage, and now we get 11, right? 19 plus 11 is 30. Okay, perfect. We're on the right track.
08:52 Sorry. So to get the leftover, what is left to get to a hundred percent is the one minus the percentage before minus B3.
09:00 All right. Sorry for them. Mistake. All right. Now we have 11. Great. We need 11 zeros. How do we get the, this is the number 11.
09:07 We need to do R E P T parentheses. And we need to, the texts we want to repeat is zero comma.
09:15 And here we go. We have 11 zeros. Great. Now what? Well, we need to split them up and we're going to put in a zero comma instead of just zero.
09:29 We're going to use split. We're going to split it by the comma or putting quotes around the comma. We do want to split it and now look at this.
09:42 We have 1, 2, 3. We have 11 zeros in different cells. This is awesome. Right? Closest conditional formatting. There we go. We add 11 zeros here.
09:55 Now, if we transpose this, we got 11 zeros down, but what's missing is the ones, or how do we add the ones?
10:07 We do the exact same thing, but this repeat here, this repeat. All right there. Two split. We're gonna combine that.
10:19 Let's let's do this in another cell so we can see what we're doing without the other stuff. So you repeat now we want to concat can catenate it means combine them with the exact same thing.
10:33 Rept but a one comma and what we want to repeat the one and how many times we go on round B, four divided by B3, times 30.
10:50 And this one, we don't want to do the one minus. We just want the percentage. Cool. So we've now concatenated those two.
10:57 And we see here, we get this 0 0, 0, 0 1. Great. Now we take all of this, we copy it. And then we're going to put it right where this repeat is here.
11:14 And now we should have 0, 0, 0 all the way down and then ones. And there we go. So we have a pretty unique solution.
11:22 We have these zeros and ones. Then we use those zeros and ones to do conditional formatting for here. Now, there was one little error that I want to fix, which is she talked before.
11:35 If we'd go over our goal, we don't want a error. We want to say we've gotten our goal. So let's say we've saved.
11:45 Let's keep adding 100, 100. Oh, there we go. What do we get? We get a value error. It says the function w R U P T, which means repeat a parameter to value is negative.
12:00 It should be positive or zero. This is really cool. So what happened is that right here in this, repeat the zeros one minus this number percentage is a negative number.
12:15 We can't repeat negative numbers. So what we could do in here is maybe do zero, right? What we want to do actually in this case of before minus R divided by B3.
12:32 When I copy it and cut it and say, if this is greater than one, then this number should be not true value is true.
12:49 If it's greater is going to be just one. And if it's false, if the numb, if before divided by B3 is either one or less than one, w I want the actual number before divided by B3.
13:04 So let's see if that works. Now we get in the name, wrong number of arguments to the split because of the expected two and four, but got one.
13:13 All right. We can figure this out. Okay. I just had an extra parentheses or I didn't put an extra parentheses over here.
13:24 I had one here. So actually it was here was the here's the problems again. So we want to let's repeat that.
13:32 We want to go before divided by B3. When I cut the F out, cut out before, divided by B3 and do, if over one, If it's false through on one, or if it's true, we want one.
13:54 If it's false, we want this and we need, and we need to add that parentheses that we hit enter. And we got the correct answer.
14:00 So we now have all ones, even though we went over our total saved, right? So now we don't have an error.
14:10 We're always going to have a full thermometer here, no matter what, no errors. If we get over our savings, in fact, we'd probably put in some extra stuff, like hidden ifs.
14:20 Like if total saved is over 1000, you could probably put in some cool, like, if that is show up some really cool things, show up, thumbs up or something we can do, like right here.
14:31 The moment, if let's do that as a bonus, as you're watching this video, if this is greater than one value of true, we're going to put a bunch of thumbs up strong.
14:46 Yeah. A bunch of thumbs up here and value with false. We want nothing. We don't want to know anything. And now it looks like a blank.
14:55 Now, if we go down, we've only saved 340. Now we'll add more 10 notes go a hundred. As we add, let's go see, let's see, let's see what happens.
15:11 Boom, thumbs up. Great. With a little, if I add a little bit of magic to our sheet, thanks for watching this video.
15:18 Hopefully you got something cool out of this weird solution to this fun problem. Like.