Excel Hour - Learn to Excel with The FP&A Guy

Welcome for anyone who's out there. We'll get started in a few minutes. We're just going to give it a couple minutes before we get started, but if you can hear me, please let me know where you're coming in from it. All right, got a few minutes before we'll get started. But if you're out there, you can download the file using the QR code or go to my website. It should be there under Excel files. Maybe you're having trouble. Let me know. If anyone has any great jokes, put them in the chat. Always looking for good jokes. Hi there Wayne, welcome. We'll get started here in a couple minutes. Think. Well I'll probably have a pretty small group today. Subject I'm covering is one I've covered before back middle of last year, so it's been about seven, eight months. I did make a few little changes but be similar to that one. So some could potentially be a repeat. If you're out there, let me know where you're coming from. Would love to know name where you're at today and if anyone has any good Excel jokes, let me know. We'll get started in a minute or two. I'm just going to give it a couple minutes. See who else joins us before we jump into things. You can download the file here. Let's see, why did the PowerPoint presentation cross the road to get to the other slide? Other, other slide? Yes. Didn't say it was good, just said it was a joke. All right. Welcome Abdul from London. Appreciate it. We'll start here in a minute. If any of you need to get the file for today, you can get it for using the QR code here or go to my website. Go to Resources, Excel files and it should be the first button there to download it. For today we're going to use the N A function. Would love to know. Hey Tarek, welcome. Thanks for joining us. Let me know where you're coming in from. So we're starting to see a few more people join us. We'll get started here in just a moment on the material. So here's kind of how we're going to run through things. I'll do a quick introduction of myself just in case anyone this first time they've joined or really doesn't know me very well. Then we'll jump into the Excel file and talk about the NA function. I would love to know if any of you are using it in the chat and how you might be using it. I think most people don't even think about using it. You think of it more of an error that you get from time to time in Excel. So we'll do that. You can see there's some examples we'll walk through. This should say min and max charts, actuals and forecast, dynamic variance, etc. So I'll do a quick rundown of myself. You know, Paul Barnhurst, I have three podcasts. I actually think when I created this there were two. So I used the wrong intro here. There should be a third one called Future Finance. I'm a Microsoft mvp FPA influencer, if you want to call that. I do training, host a bunch of podcasts. I love talking and sharing things with people and helping people be better finance and FPA professionals. And that's a little bit about me. We'll look at the rest of that later. So I'm going to hide that for now and we'll open the Excel file and get started. So you should have an Excel file using NA in graphs. So I'm going to ask question, Love a comment. Anyone using NA for anything in their in Excel today? And when I say na, let me show you what I'm referring to. Make sure we're all on the same page. So there is an actual function in Excel called NA that has no inputs. You just put an open and closing parenthesis and it gives you the hash NA error. So there's only a few formulas in Excel that work similar to this in that you don't have any inputs. You strictly just put open and close like today. Now you know, NA is one and it returns the NA error. So where, where this can be helpful. Let, let's just show a couple examples, then we'll talk about charts, which I think is the main use case. So here if I have the number one, two, you know, a blank and three and someone's looking at it and they want to know, hey, should this be zero or is it not applicable? You could, you know, you could put an NA in here and it would signify, hey, it's not available or we don't have it. So often it's a way to input data to signify that the one problem you're going to get when you're dealing with numbers and not graphs is if I take an average here. So if I go average with na, I'm going to get the NA error. But if I make this blank, then it's going to take an average of just the three. If I make it zero, it does an average of the four. And so, you know, N A is great to identify it, but it can cause problems. So if we put this back here, I'm just going to stick this N A in, you know, here I could try something like subtotal, average or aggregate. So help. Let me do aggregate here. Average and ignore error values. So let's see. I think if you do that. Oh, it helps if I put the array I want. So see here I could now use an NA and still have it give me the answer. But what it does is it just ignores it all together. It doesn't consider it at all. So you can question if that's what you want. But that's, that's kind of a use case that you might use in the grid. Where I see NA and I use it all the time is with graphs. So here's an example and we'll walk through how to do it. By using na, I'm able to create this graph here that has three different data series but looks as if it only has one and highlights the largest and the smallest. So if I was to change. Let's change this month here to 3,000, the red automatically moved. If I change this to 50,000 now the green and red moved because the N A helps with that. And I'll explain why in a minute, you know. Next I have this graph I've created here that again is using N A that has actuals, then it has forecast. It's actually two different graphs but I've used N A to help me build it. So it looks like I have one graph with a completely different look for the forecast and the actuals. Next one here and I've done two of these. So this first one I used the new check boxes. Love these. Go to insert right here there's a checkbox. So notice I have this graph and I'm actually going to take this part out for the moment. I have here cells and I want to compare different things with cells. I can come here and click on or off and say, oh, I want to see just cells. I want to see cost of goods sold, gross profit customers. In this case, I am using the NA function along with the checkbox to do that. Here I have compare cells with cogs, gross profit customers. And in this case I am using the forms control checkbox with NA to allow me to choose which graphs I want to display. Well, I'm gonna pause for a minute. Any questions so far from anyone? Anyone got any good Excel jokes for me? Got a qu. Quiet group today. I think we have a small group out there, but love to know where you're coming from. If you have any good jokes or any questions so far? I'll just give it, give it a minute and then we'll keep going. All right, so next we have this variance chart. This is one I learned from Lee Lani. Fabulous work, by the way. And so you'll notice, I have these nas I'm using TO with the greater than zero, less than zero. And you can see how the chart looks here that I've built. I don't understand. I'm not. Can you elaborate what you mean, Lemon, when you say you don't understand? Don't understand what NA is or. I need a little more context to try to address what you're not understanding? Abdul says, good so far. Yeah, I didn't. I, I will explain that later, Lemon. I'm just showing examples that they can be used together. We'll walk through it. So bear with me. By the end you'll understand. But high level, the check boxes, you can see right here, the graphs are changing and the key to make it work is the N a function. So welcome from France. I'll explain why a little later. Adam, the Excel files on my website, as I mentioned at the beginning, you can go download it resources, Excel files. So the, the, the file is always there. I always mentioned that at the beginning. So you can, you can go pick them up there. So let's go through and walk through an example. So we're going to create this right here. So what I'm going to do is I'm going to come over to the Maximin walkthrough. We're going to start there and I'm going to enlarge this. You can see I have the month and the gross cells. And so I want to go from this to this. So let's just kind of start adding here what we want. So I'm going to have a max cells and a min cells. So first what we're going to do and then we'll walk through. So before we even use NA or write a formula, right, I have my numbers here. I'm going to go ahead and copy the formatting from here because I don't want to redo it. There's some custom formatting. If you look, you can see in thousands. So I'm just going to copy this, take it over here. And now I have that formatting. I could delete the color here, but I, I went ahead and copied it all. Now I'm going to put this in a table. I'm going to say contrl t and we'll get rid of that there so that I'll just say no fill. So now I have a table and it looks like I made a mistake. I didn't get my headers in my table. So let me undo that contrl t. My table has headers. There we go. Now I'm just going to get rid of the format here. So we'll just go ahead and get rid of the fill. And so I have this similar to the other one. So if, let's just say right here, let's manually go. And I say, okay, I know this is the max cells, so right, I could set this equal here. And I'm going to hit undo. I'm just going to set the one equal. So I want just this cell right here equals C15. And what happens is I'm using a calculated, I'm using a table so it tries to automatically fill in everything in the table. So I am just going to hit control z undo and say no, just do this one. And let's say I put a zero for the rest. For the moment, this will help you understand na. Then we'll walk through the rest of the example. So I'm going to take this now and I'm going to say I want to create a chart with month gross cells and max sales. If I didn't want month, I could add another one here called period like I've done here. And I can display the period either way. But for now we'll just say date. So I'm going to come up here, I'm going to insert chart. I'll click on recommended insert a line chart. Notice here with this line chart, right, we have 00000 and then all of a sudden we have 37, 000. Doesn't look, doesn't look attractive at all. Right. Kind of a mess. But now let's go ahead and just type in equals NA to each one of those. That is not the max. Notice what happens here. Now I have just the one point for max cells. It's covered by this. So what N A does with the graph is it says, hey, that's an error. I'm not going to display it versus having a zero or a blank or something like that. See, if I had zero here, blank there, it doesn't, you can see issue. So what we're doing is, is we're using the NA to have it not display except for the number we want. So now what we're going to do is we're going to write a formula to do that. So the first thing we're wanting to do is figure is we're wanting max. So you know, max, if I go equals max. Oops, if I can type here, equals max. And I'm going to highlight the whole thing for one moment, you know, and I get the same answer on all of them because I've said max, not exactly what I want. And so now I can use an if statement. If max of this is equal to this cell here, return that cell. Otherwise we can say give me that hashtag. So I've now written a simple, simple if statement with a max. So what I've done is if the max of the gross cells is equal to this row, that at symbol means look at each individual row, then return that number. Otherwise return the not applicable error. I close it and I have my max cells now. Now I would do the same with min. So I'm going to copy this formula, paste it here and just change it to be min instead of max. The only thing I have to do, change change two letters in it. Everything else is the same. So now I have the max and min. Next I'm just going to copy my formatting for both of these. And so now we're going to delete this and recreate it. Now I'll explain why I put it in a table here in a minute. So let's go ahead and insert. I'm just going to go recommended chart, come down here and select a clustered column. So now you can see max, but still not very close to this. Still have a ways to go. So we'll walk through the steps of how to get it to look like that here in a minute. Two things I want to point out first before we do that. First, the reason I put it in a table. Notice If I add 2023 to the bottom, it automatically recognizes the table and adds it to the chart. So if this was my new low, if I made this a thousand, you can see all right. It's also going to have the min over there. We'll explain in a minute, but it makes it easy to update our data. So I'm going to delete that one for now. So that's the first thing I want to show. The second is, let's say we already have a, a chart with the format we want. We can always come here and copy that format. So I'm going to highlight my chart right here, hit control C, come over here and go to paste special formats. And I just did the chart, make it a little bigger so the dates show up. I can play with that. Make sure the you know, the dates show up. There we go. And I'd be done. Now I'm going to walk you through how I did it. Not use paste special format. So we're going to undo. Come back here. Okay, we have it like this. We're going to do a few things. Anyone who knows me knows I hate lines as a general rule. So I'm going to get rid of those. I'm going to get rid of this. I don't really like these diagonal. That's why I often put periods here. So maybe I'm going to say, you know, I do want a period. So I'm going to add. Let's see, just going to add a row here and I'm going to expand my table. So I'm going to resize my table to include B. I'm going to come here and I'm going to say B. I could have also right clicked and said insert a column to the left. But I'm going to add one. I'm going to call it period. We'll say period and we'll just put, you know, one and we can two, whatever. There's different ways we could ag. We could put a sequence, a row, formula, whatever. But we'll just keep it simple. And there it is. So now you can see it kind of messed this up. So we're going to go back to our data and we're going to fix that. So let's go ahead and edit this. We want just the periods. That's good. So what I might do. And then I need to remove the month. So now instead of months, I can just see periods. And the only reason I did that so it's much easier to read than doing dates there. Okay, I'm going to get rid of my title. I'm going to get rid of these axes and we'll fix it in a minute. You'll notice it has two axis at the moment. We don't want that. So now we're going to go ahead and go to change chart type. We want all three to be a clustered column. You don't want a secondary axis for any of these. So we'll click that off and make sure all three are the same. So all three should be a clustered column. There you go. Now we need to get this max and this min on top of these numbers. So we'll come here and what you could do is highlight the green or the red, hit Control one and that's going to bring. Bring up your format data series. So to get these to look like they're all one series. We need to do our overlap. So we're going to come in here and we're going to make our series overlap a hundred percent. Now it looks as if we just colored max and min differently. What we really did is we created two additional series. If I look at my data, I have three series. I have gross cells with 12 numbers. I have max cells with 11 errors, N A errors and one number. And I have min cells with one number and 11 errors. Those numbers automatically update based on the. The formula we've created to be whatever the max and min is. And we've put them on top of the other series, and we've used a different color for them. So this series here can have its own color. Now, the only other thing I'm going to do here is I'm not a huge fan of the gap width. I don't like skinny pencils. I like them to show that they're relatively close together. I think it's a little more aesthetically pleasing. I usually do a gap width of around 60 or 70. So there you can see I have a gap width. I would also get rid of this border, you know, put a title on it. But there's the first example of creating a max and min using the NA function. So what I want to do is I want to pause. Give it a minute before we go on. Any questions from anybody on that, on what we just did there? Anyone need me to show something again or just let me know in the chat? I'll give you a minute here. In the meantime, I'll tell a joke while I'm giving a minute to see if anyone has any questions or comments. Does anyone know what the Eiffel Tower and a tick has in common? So the question is, does anyone know what the Eiffel Tower and a tick has in common? They are both Paris sites. All right, there we go. So let's go to the next one here. This one gets. I think I'm gonna go ahead and we'll come back to actual and forecast. I want to do the dynamic chart walkthrough. I know, Lemon, you asked about this one. Let's walk through this one. So here we have data. I have month sales, cogs, gross profit, and customers. Right. If I put this into a chart, I could come insert here recommended charts. That's really, really crowded. But if I want the ability to choose what I look at, right, What's a way I could potentially do that? So that's what we're trying to accomplish. So remember, if I Don't want. If I don't want cogs to show here, I need the na, right? So if I went here and put NA to all these, that one went away. So before I even do this chart, we're going to create something that does that. So let's take each of these cells is just going to be equal to this really simple, straightforward cogs. We need to somehow say, hey, if it's true, give me the cogs. Which means if it's vis, you know, if I want to show it, give me the cogs number. If I don't want to show it on the graph, I need N A. So an easy way to do that is a true false statement. So now what we're going to do to make that work is we're going to insert check boxes. So let's, let's go ahead and insert our checkbox. I'm going to undo that. We're going to do just one right now. Let's insert one checkbox. Now what I want you to do is hit check. Notice what it says here. True, click off false. So what I can do is I can set this equal to this right here. Now I have a true false, don't know what. This shouldn't be here. I'm going to delete that. Come over here. We'll call it cogs. So I can write a simple if statement that says, hey, if true. And it's automatically going to know. I don't even have to put it equal to true. Do this. Otherwise give me na by default. Since it's an if, it's saying, oh, that first one true, second one false. So since the word's true, it's going to assume this number. So now I just need to lock my 21 here and I can drag that down. And using na, I have now created that. So we're going to do that for each one of them. So I'll insert a. You know, maybe we'll put it down here, put a second checkbox in. We'll call this one gross profit. We'll do this right here. And we just need to set it equal to that. So we'll make it i6. We'll copy this formula over. We'll have to clean it up a little bit, but yeah, right there. Yep, all good. We didn't need to clean it up. Sorry. And so now let's copy this down. I mean, if we wanted to, we could have even done this right here and done all. I think we could do all of them at once and make it dynamic. But it's usually a little easier to follow if I just do the traditional one row at a time. But just want you to see, I could have even written this as one formula. I could write this whole thing of one formula now with Excel if you wanted to. So we'll just go back and drag it down. We'll do the old fashioned here. So we've done that one. We're going to need to do one more. We'll copy the formula over. Obviously, right now this is going to consider it false because there's nothing there. So now we'll add our third checkbox. Add our checkbox. Call this one Customers. We'll set it equal to our checkbox. Now we get the numbers. Ah, Shruy has a joke here for us. I just saw that. What do you call an ant who's great at Excel? I don't know, Truthy. Let us know, we'll find out here in a minute. I know sometimes there's a little lag between when you type something and when I see it. I just barely noticed it now. So we'll get that answer here in a minute, but I'll keep going. So now we've created our data set that works with these checkboxes. You can also do this with the forms the old way. Like let's say you don't have checkboxes for some reason. You can come over to developer and insert a checkbox and, and basically create the same type of thing. You can, you can add a checkbox. You call it an Excel ant. Well done. All right, so now the rest of this is really quite straightforward. Just a matter of creating whatever graph you want to use. Let's say maybe I, I'm just for fun, I'm going to say I want a stack column. I wouldn't recommend this, but let's just see what happens. So I have a stack column here, right. See, I could take all those out, but really they're separate things, so they shouldn't be stacked. So we'll go to our change chart type and we'll say separate columns. That makes more sense. Now I can turn on and off what I want compared to cells. And so you can see, you know, you could use that with different graphs. Like maybe you had multiple graphs and you wanted cogs to display on all of them for something you're looking at, or none of them, just depending on what you're doing. Or maybe you want to exclude a certain department. There's different ways you could use these check boxes and true faults to modify what People are seeing. And so we've walked through two examples. We're about halfway there. The. The. The other two are actuals and forecast and variance chart. So I'll let people tell me. Which one would you like to see next? I'd love to get some thoughts. Do you want me to do the actuals and forecast walk through here, or do you want to see this variance walkthrough? So that would be. This would be the variance chart. This would be the actuals and forecast. So I'll give you a minute. Just kind of let me know in the chat if you have a preference, and I'll ask Lemon. Did that make sense, what I showed there the check boxes now? All right, give it about another 30 seconds. If I don't see anything come up, it could just be. All right, I'm getting variance chart. Variance chart. Variance, please. There seems to be a theme. I'll definitely do actuals and forecasts. No, I'm kidding. All right, so the variance chart is two charts. You have your variances here, which can be done as the bar chart. I use a different method. I'll walk you through it. Gives you a little bit more control. It is quite a bit more complex, but I'll still walk you through it. I learned it from Leela Gurani. If you click here, you can go to the video and watch your explanation. Much better than mine. But where we're going to start is if in our variance walkthrough, we're going to add the. Where we want to know greater than, less than. So if I come back here, you can see what we've done. We have, you know, C4 divide D4. So we're figuring out our percentage. Percentages. So let's go back and figure out our percentages. Right? C4, divide D4 minus 1. Right. So if I just drag that down right now, whether it's. Let me make this a little larger. Whether it's negative or positive. Sorry about that. Whether it's negative or positive, it's all in one line. So now I need some way to tell. To only show the positive. So if C4 divided D4 minus 1 is greater than 0, and that should be greater than 0, then give me C4, divide D4 minus 1. Otherwise I'm just going to stick a 0 in there. All right, so. Oops. So we can see now that worked. But we know we don't want zeros. So NA is going to come to the rescue. We're going to change this to NA and we'll drag it down and that works. Notice given this is all dynamic, I could even do do this all as one formula if I wanted to. Just want people to kind of. I think it's important to really realize how many use cases dynamic arrays have. Right. I just did it as one formula for now. I'm going to go back to the other method. So we'll come here, make sure we copy it down. All right, we've done that. So now I can just copy this formula and put it right here. So here I'm going to stick in the equals and the only difference is I want less than zero. All right. I want each of these to have percentage and one decimal point. So I just came up here and changed that. I could have done control one and did it. But now my data looks the same as my variance chart. Right there's my data. So the next step we're going to do is we're going to create the first chart. So notice if we go back to variance, this is just the 20, 23 data. So we'll come here, we'll highlight this, we'll go to insert, insert a chart and we have our data. I don't want to sit there and make all the changes. So we're going to do this. We're going to come here to get our formatting the same. I'm just going to do contrl C. Come here pay special. I could do alt e s but I'm just going to do paste special, select formats. And there we go. Now I'm going to make it a little bigger and I have my first one. I could change my title. Not going to worry about that at the moment. But we've done the first chart. You know, we can play with the size however we want. I like that. So I'm gonna pause here. Any, any questions so far? Everybody with me? Give you a minute to. To ask any questions. Should be hopefully pretty straightforward. All right, so if no questions here. We now have this to do. So I want to show you something. If I go to select data, you'll notice what I have here. But you can see these are the data points. Makes sense. But go to the chart type and notice it is a scatter that's been used. Doesn't look like a scatter plot to me. But that's what it is. So we'll go ahead and highlight this insert. Go to recommended charts, Go to all charts. See Scatter. We want this scatter, right? Let's see. All right, so we want this one right here. Then what we're going to do is go into select our data didn't quite do what we wanted. So we're going to need to pick our data series. So I'm just going to go back and show you here. If you look here, you can easily see, hey, that's positive and there's the Y value. The other one's negative. So we're going to do the same. So we'll go select data, we're going to edit it. We want this to be positive. I'll call that the series name. We're going to delete this altogether and we're going to select just our positives. So now I have that. We'll add series name is negative. We'll select just this. And now it doesn't look anything like what we want. So let's see if we got the right. See, we're going to do there, we're going to do that chart there. So what I did is I actually changed the chart pipe. I. I think we're going to use this scatter plot. That one didn't. All right, so now you can see the negatives and the positives. There's a couple things we'll do to get rid of this. We're going to get rid of this. We're going to get rid of this. And we're going to get rid of the labels here. So I'll hit control one, come in and say, none for labels. And it should have. Why did it. Okay, there we go. All right, so now we have dots where each of these should be. We want them to look like bar charts. This is kind of where the fun comes in. I really like this solution. It takes a while to get used to. But what we're going to do right here we have all these. We're going to come over and click plus, we're going to go to error bars and we're going to add error bars. We could even add data labels right now, or we can add it later. So I have these error bars. It's like, okay, that doesn't do me much good. We're going to get rid of the horizontal error bars. So we've got rid of them. We're only doing it on the positives. Now I'm going to click on an error bar and hit control one. So the first thing I'm going to do is I want error amount. Percentage. Percentage, yes, I believe 100% minus, no cap. So let me do that again because it is a little confusing. So I'm just going to hit undo. I have this right here. I'm going to come in and hit minus, no cap percentage, make it a hundred percent. Now I have a line. Still doesn't look like a bar chart, but I have a line. So now to make it look like a bar chart, we're going to come in here and we're going to make a thick line. So I'm going to make this error bar. We'll make it that thick there, and it's positive. So I'll change the color to green. Now I have that. Now what I need to do is I need to get rid of the marker so I can click on the little dot. Come over here to marker. Marker options. Say none. I've now created it. I can change my labels to be a green font to match, and I can bring it down here, and you just have to, you know, drag it and align it. Now I have to do the same with the other side, but I've now created it and then I would get rid of the. Get rid of the outline here. So we'll go to format, no outline, and then just kind of make sure they're all lined up like, all right. Not. Not really lined up yet. There we go. Bring this back a little bit. All right. That's about right. Toyota, Honda. A little bit of a pain. Like I said, I could keep playing with that and kind of get it all lined up perfectly. Once I have it all lined up, I'm. I'm good. And I can group it and combine them together. So then the next thing I'll do is I do the same thing down here as I did above. Come up here. Make data. Make not data lab. Yeah, Data labels and error bars. Get rid of the horizontal error bar. Come into the vertical error bar. Hit control one to bring this up. Do minus no cap percentage 100%. I now have my line. I can go to my fill and make it thicker. I don't remember what I did on the other one, but that looks roughly right. So I'll call that good. I'm going to make it red. We'll do the dark red here. I'm going to get rid of my marker. I'll come here. Marker options none. Now I need to fix the labels. So I'll come to labels my label options. I've clicked them. Going to come over here and say where do I want them to be? Right now it's saying above. I want them below. So I'm going to change that to below. I'm going to change it to red, the font. And now I have created the variances really handy chart. And I can just, you know, click on both of these and I can group them together. I'd love for it all be one chart, but that's the way I created. So let's say I change a number here. Let's say this now becomes right. 2.5 million. Why did it. That's. There we go. Took it a minute to refresh. Sorry, my computer was running a little slow. So there we go. You can now see the 36%. You know, if I change this one to 200,000 now, that completely changes it. It adjusts everything. So that. That is the variance chart. I'm just going to hit control Z to go back to what we originally had here. There we go. All right, I'll give you a minute. Any questions anyone has or good jokes was. We're waiting to see if there's any comments. I mean, really, the key here, all NA's doing is hiding things you don't want to display on a graph. It's the easiest way to do it. Simple function. Then from there, it's experimenting and finding different ways to visualize your data that will really help the audience. You're welcome, Lemon. You enjoy your meeting. If you're in France, that's the late night meeting, so good luck. All right, so real quick, we'll look at the last one and I'll. I'll walk you through the logic. And then I think I'm going to leave this one for you to try on your own. I'm going to run through a few other things and we may wrap up a few minutes early if there's not questions. I know we have a smaller group than normal today because I think some people have attended this before. I've talked about it. So this last one, I really like this when you're. Hey, I. I want one line for actuals and forecast. I don't want actuals here, forecast here. I want to just be able to see them, but I want them to look different. I want them to stand out, and I don't want to have to manually go through and manipulate everything. So if I add a. Let's say I add a new actuals here. You can do it with me. I add 400. That updated. I had 500 updated 600. Right. And it keeps updating there automatically. That's really handy. So basically these first three, this is our data. If you were to look at the walkthrough, you can see our data, then what's being done is the first one's a simple if statement. If this is actuals, so right here it's if D4 is greater than zero, make it actuals. Otherwise do forecast. So, right, if there's a number here, you could have greater than less than. If there's possibility to have a negative number. But all it's doing is looking and saying, oh, there's a number, it's actuals. There's not a number, it's the forecast. So then what's happening is we have an if statement that looks and says, hey, if b4 says actuals, give me actuals. Otherwise give me n a great works. That all works. But there's one thing you're going to notice here, and this is where it gets a little tricky, is notice there's a 365 here, and there's a 365 here. So the last month of actuals has it in both. If I take out the 365 here, notice it doesn't look right. Right? That's not what I want. I don't want a gap. If I take it out here, I have a gap. Now I could say N A, you know, equals N A and I, but I still have a gap, you know, if I say 365 here, still got that gap. So I need it in both spots. So this formula becomes a little trickier to write. And I did an if. Probably should have been in ifs, but I'll walk you through the logic. So if B4 equals G3 equals forecast here, which really probably what you know, right? Just like this. So if it equals forecast, give me forecast. So that does all these. Then the next one says if the max of C14 to C15, right? You can see the max ifs, that's the range here, is the max actuals. Then give me that number. So the second one saying whatever the last actuals is, because these are just numbers, you know, June is the biggest number. Go ahead and display that. Otherwise give me F, you know, then give me F4. So then give me the actual. So basically, if this greatest number where there's actuals is equal, then give me the actuals number in my forecast. Otherwise give me NA. That's going to fill in the 365 and allow the line to be continuous. So I have the two 365 right here. And that, that's the basic idea. So this, this formula is the most complex part of it. And like I said, all, all this step's doing here is making sure this displays 365. Or I mean, forecast here displays 365. Even though it's actuals, this one gets displayed by the fact that this says actuals. This one here looks at it and returns the max actual. And that's it. Once you've done that, all you got to do is plot it and make each line look different. They're connected, but they are separate series. Right. If I hit select data, I have series one and series two. They are separate series, which now allows me to display them differently but treat them as one one series for visualization purposes. Right. So that was the last example there. You guys can walk through that one on your own. But I wanted to kind of let you see the logic, so feel free to practice it before I head back to the deck and we cover a couple things. Any questions from anybody? This has been a very quiet group. You can see the cute little squirrel there on my desk. I'm going to stop sharing for one second here. Any questions from it? Anyone? Before I go back to the deck and we wrap up? Come on. Gotta get at least one question. That way I don't feel like I'm speaking to the. The empty air. One question or one joke? Somebody help me out. Can't ask Lemon. Let's see. Shruthy, do we have another joke? Is Adam out there? Muhammad, Somebody give me something I'm not sure I understand. Wayne, when you say, would you have to reset it every month, what do you mean? All right, hold on. Let me bring back up the graph here. See the question there? So we'll start here with Truthy. 362 is the forecast. Ruthie, we want Actuals, which is 365. That's why it's showing 365 in both of these. So the last actual, whatever month that is, if that's January, February, whatever. We need both actuals and forecast to be actual so that we don't show a gap here. It's so that they meet there. And so you'll want it to be the same number. No, I don't see why. No, you wouldn't have to reset the. The line chart every month. You could put this into a table and add additional lines. So let's just say. Let's say I make this a table for a moment. And it didn't like something. What didn't it like? I would have to look at it. But you could do a range or a table otherwise. Yes. If you didn't have in a table or range or something. Like if I just typed 1, 1, 20, 25, and, you know, say my forecast was 500. Copied these formulas down. I'm now going to have to come in here and change my data. Right. To include that. So either putting it in a table or having some kind of, you know, dynamic arrange for it would allow you not to reset the graph. Otherwise you'd have to reset it every time. Does that answer it, Wayne? But you know, in a typical rule in this example when you're doing budget and forecast, you're usually comparing a year at a time. So I don't know that there's a big need here where you're going to be constantly adding. I mean, I guess if you're trying to do a rolling forecast where you always want 18 months or something, but otherwise you'd be good. All right, gonna go ahead and close that. We're gonna go back to the deck real quick and just wrap up. So that covered that. Last thing I just want to do is put in a plug here. Let me present my screen. All right, so there are two course offerings I offer here. During this webinar, you can use the code webinar to save 30. I have a 10 hour combination of four different courses called the FPA Ultimate Course Bundle that you can take. It's normally 129.99. You'll save 30% with the code. It includes all the Excel training. It's the modern Excel that's used in the FPA certificate for Wharton. A lot of their material, they licensed what I done here. There's a data analysis course, modeling, design and business partnering. If you're interested in just the Excel, you can take three modules here. It's 59.99. You get lifetime access. If new modules are added, you get those. You can also take the Excel tables for free. You can find that information on my website. You'll see the codes here. So that's an offering I have. You know, I try to do as, as much training as I can for free, but I also have to pay the bills. I'm sure as you can all appreciate. So that's all I had for today. I'll hang on for a couple minutes. I know there's a little bit of a delay between when I ask something and get a response and see if there's any questions or anything anyone wants me to answer. So I'll hang on for two, three minutes here and then we'll wrap up. So thank you. You're welcome to drop if you want. But if you have any questions, I'll be here for a minute or two and hopefully you you learned a way to use NA that you've never done. You're welcome, Shruy. I'm glad you found it helpful. All right, gonna give it about just one more minute and then we'll gonna hang up here. So feel free if there's any questions. You're welcome, Adam. I'm glad you found them helpful. All righty. I'm going to go ahead and hang up, but please get a chance, go to my website. Anyone who can, you know, subscribe to my channel or leave any reviews always appreciated. Helps as I continue to look to grow my business. But thank you for attending today, everybody. Have a great morning, afternoon, evening, wherever you're located. Thanks. Bye. Veronica, I was just gonna hang up. I haven't done a live session on a rolling forecast. I mean, I talk about forecasting from time to time. Feel free to message me. I want to get a better understanding of when you say a live session on rolling forecasting, what exactly you're looking for. I don't know if it'll make sense for things I do, but, you know, send me a note. I can always look at it. So thank you. I'm going to drop now.