M 365 Excel Worksheet Formulas & Models – Everything You Ever Wanted To Know - 365 MECS 03

welcome to video number three in microsoft 365 excel the complete story last video we had an introduction to worksheet formulas but in this video we're going to have a comprehensive session on building worksheet models with formulas and [Music] functions now here are the 18 amazing topics we're going to cover in this video and a lot of what we do will expand on what we learned last video about worksheet formulas and some of the important topics we'll talk about modes of cell editing types of formula like array formulas or single input single output formulas we'll talk about number formatting and style formatting as it applies to building a worksheet model that means we're going to cover a lot of worksheet formula fundamentals before we even get to our awesome six models we'll build four different worksheet models and then we'll look at two more advanced models all right let's go click on the sheet formula elements this is the same list of formula elements we saw in last video the last excel workbook and the last pdf notes but guess what on the next sheet i included this from my new book the only app that matters this is a complete list of every single possible type of formula element and as we encounter these different elements we'll talk about them also on cell references here's a list of cell references as we encounter these types of cell references we'll talk about them by the way all of these notes here are in the pdf notes for video three now what we want to do now is go over to types of formulas now we've already talked about number formulas that's a formula that delivers a number text formulas delivers text logical formulas delivers true or false called a logical value or a boolean value now those are formulas by data type but in this video we want to talk about formulas by calculation type now last video we talked about what an aggregate formula is that's when you take a bunch of numbers and deliver one answer like adding or averaging we're also going to learn that there's a formula type called single input output formula that's a formula where there's some operation like multiplication or some function and in both cases after the operation is run it delivers a single answer the single input part is there's a single item on either side of the operator where there's a single element entered into a function with that single input or inputs a single output is delivered that's different than an array formula array formulas have multiple items right here we have b1 to b5 that means there's multiple items on one side of the operator like a multiplication symbol or there's multiple items put into a function in both of these cases these operations math and function will deliver an array of answers now there's two types of array formulas dynamic spill array formula that's where the array of results are spilled into the cells we saw an example of that last video a scalar array formula is where internally inside the formula there's an array operation that math operation delivers multiple answers but the values that are delivered aren't allowed to spill into the cells because there's some in our case an aggregate function that consumes those array delivered values and then delivers a single answer so all array formulas contain one or more array operations that deliver an array of answers but whereas dynamic spill array formulas spill the results into the cells a scalar array formula internally has an array operation but then that resultant array is used to deliver a single answer now the reason we don't just call this an aggregate formula right here is because internally there's an array operation and we need to know that because if you have a huge array operation working over hundreds of thousands of rows that takes a long time to calculate and might really slow down our worksheet model so it's helpful to know up front that we might encounter this problem so whether the formula spills into the cells or we get a single answer they're both array formulas now there's one last type of formula an excel table formula and we'll see an example of that coming up now let's go over to the sheet types f and we're going to talk about each one of these types of formulas now we're going to look at one two three four and the five different formulas we just talked about but before we do that in the last video muhammad asked a really great question and i'm going to answer it now i temporarily change the dimensions of the workbook because i want a close-up look of creating a formula and what happens down in the status bar now i want to create a formula in cell c9 and before you create a formula the status bar shows ready that means excel is ready for you to create a formula to create a formula we type an equal sign and when we do that the status bar turns to enter that means you can either go and get cell references or type other formula elements now if i use my mouse to get the cell reference the status bar says point and that means i can point and move the selected cell to wherever i want if i click escape when i type equal sign i see enter when i type left arrow i see point and again that means i can point to whichever cell location i want then i'm going to type a multiplication symbol i jump back to enter but watch what happens when i either intentionally or sometimes we accidentally take our i-beam cursor as soon as i click with my i-beam cursor in the formula it changes to edit if i click at the end now and try to use my arrow keys to go get a cell reference well it doesn't work the arrow keys when you're in edit mode move through the edited formula so muhammad asks well when i'm in edit mode how do i get back to enter so i can use my arrow keys to get cell references and the way you do it is you hit the f2 key when i hit f2 edit changes to enter if i hit it again it's a toggle so i hit f2 when i see enter now my arrow keys will work once again so up arrow to get my cell reference then i can hit enter now over on modes of cell editing there's some notes and these are also in the pdf notes all right let's come back over to types of formulas and i'm going to hit the delete key all right we have three different types of formulas and in all cases our goal is simple we have some sales a tax rate we need to calculate the tax rate for each one of the sales and then total them at the bottom now the single input output formula method this is the method we've been using for 40 years this dynamic spilled array formula this is brand new in microsoft 365. it also works in excel 2021 and table formulas have been around for over a decade all right let's start with the really old school single input single output with these types of formulas we're going to have to enter a formula and manually copy it down not only that but the formula that we create will have to use a relative cell reference and an absolute or locked cell reference that's much different than a dynamic spilled array and it's one step more than we'll see with the excel table formulas so we type an equal sign left arrow that cell reference is a relative cell reference when i copy it down it'll move to the next sales amount then we multiply up arrow to get our tax rate now as this formula sits right now that c6 is not c6 it's really i'm always going to look one two three cells above that is also a relative cell reference that's not what we want but i'm going to enter this formula and show you what i mean now because the next action is to copy the formula i'm going to enter the formula using control enter that puts the formula in the cell and keeps the cell selected now the fill handle in the lower right hand corner i'm going to point to it not my selection cursor not my move cursor i point to it until i see my cross hair or angry rabbit cursor then i click drag down and let go now anytime i manually copy a formula i have to select the last cell hit f2 and verify if the cell references are pointing to the correct location well this one's pointing to the correct sales amount and that's a relative cell reference up here it was looking one to the left down here it's correctly looking one to the left but that orange cell reference is not correct it's supposed to be looking at the tax rate in c6 but we left it as a relative cell reference which is i'm always going to look one two three cells above in the top cell it was correct because the tax rate was three cells above but right here it's looking at an empty cell which is zero so control enter use the delete key click in the top cell and i'm not gonna delete this i'm gonna replace the formula by typing an equal sign left arrow multiplication symbol and up arrow three times now to convert this relative cell reference to an absolute cell reference also called a locked cell reference we use the f4 key when i hit f4 it puts dollar signs in front of the column reference c and the row reference six those dollar signs mean for the c if i were to copy it this direction it's locked on c and for the 6 the row reference that dollar sign means as we copy it down it's not going to move from row six now we can enter the formula with control enter point to the fill handle with our angry rabbit click and drag immediately click in the last cell and hit f2 you want to verify that the cell references are correct and the relative cell reference is looking at the correct sales and locked cell reference c6 is looking at the correct tax rate so that means that a relative cell reference moves throughout the copy action but an absolute is locked throughout the copy action if i simulate the copy action with f2 enter f2 enter notice the blue one is moving the orange one is not that means when you use a single input output formula and you're copying your formula you always have to consider what type of cell reference to use that'll be different when we get to dynamic spilled array formulas because we can accomplish the same goal without worrying about relative and absolute cell references now let's hit enter and we need to add so we'll use the keyboard alt equals the sum function has the correct range so we hit enter now anytime we make individual calculations and then use those calculations in a subsequent formula we have to consider whether or not to use the round function and the three requirements for when you must use the round function are these first off are we required to round well this is money so of course we have to round to get these amounts to the penny position the second thing is well do we have extraneous decimals well yes we have decimals past the penny position and in fact we didn't even have to make these calculations to know that we were going to have extraneous decimals because when we multiply these this tax rate already has decimals past the penny position and the third and most important thing is if we're making these calculations and using them in a subsequent formula well that formula is going to see all of those extraneous decimals and the formula may give us an incorrect answer now i want to show you what many people do who don't know how to round they take their selection cursor highlight control 1 to open up format cells they say well i'm going to use currency and i can already see right here in that top cell it looks like it's 44 pennies so it looks like it's rounded when i click ok well i see the correct amounts and i see the total is 2.03 pennies but remember even if we use this number formatting that formula right there sees all those extra decimals underneath the number formatting to see that this can lead to errors let's click in the top cell hit f2 and just like last video we type round i see the function in blue from the drop down so i hit tab number well that's the calculation right there that's the number we're trying to round i i-beam cursor at the end click type a comma i see number of digits that's the input where we tell round which position we want to round to this is the penny position so i count one two so type two in number of digits close parentheses now when i enter this and copy this down i want you to notice two things first we're editing this formula so we're required to enter it and re-copy it down when we get to these other two methods we will not have to do that the other thing is as i copy the formula down i want you to look at the total control enter and i'm copying it down when i let go sure enough that two dollars and three pennies was not correct the correct answer is two dollars and four pennies now to prove that the round function actually worked i want to highlight the entire column using my selection cursor and right now if you look up in the home ribbon tab number group we can see the currency number formatting is applied but i want to remove that currency number formatting and in fact you can remove any number formatting that is applied by using the general number formatting now you can apply it from the drop down you can use control 1 and apply it there or you can use the keyboard to apply the general number formatting now you're going to have to look on your keypad and find the tilde grave accent key it's to the left of the number one so let's do it control shift tilde grave accent and just like that general number formatting has been applied you can verify by looking in the drop down we can see that the round function in fact did round to the penny and we have the correct total now you can think of the general number formatting as an eraser that wipes away all the number formatting so we can see the actual numbers in the cells now we wiped away the number formatting just to prove to ourselves that it worked now i'm going to control z because i want to keep that number formatting there now let's create our dynamics build array formula here and keep in mind the steps we had to do when we created this formula we had to consider cell references we had to manually copy and if we edit later we had to recopy it down the column so we click in the top cell type an equal sign and instead of selecting a single relative cell reference we highlight all five values then we multiply times the tax rate and we do not have to lock the tax rate and because we have a multiplication operator with five items when this evaluates it'll calculate all five amounts and spill them from the top cell down below so let's try it let's hit enter and that is amazing it's spilled down and we didn't have to manually copy it now when you create a dynamic spilled array formula the formula only lives in the top cell in fact let's click in the top cell sure enough you can see the formula there but when you click in any cell below the formula is grayed out that ghost formula does not live in that cell and in fact if you hit the f2 key sure enough there's nothing actually in the cell however if you type something and then hit enter the formula from the top cell is polite it says i cannot spill the results because there's something in the way so when i delete this sure enough now it can spill every single cell below the top one there's a ghost formula the formula only lives in the top cell now the third big advantage is we need to edit this so we simply hit f2 we're gonna round these amounts right in number we have a multiplication array operation delivering five answers that'll force round to deliver five answers at the end comma two close parentheses and instead of entering and manually recopying it all we do is hit enter and the edited numbers spill into the cells so in the top cell f2 the advantages of the dynamic spilled array formulas are didn't have to worry about relative or absolute cell references didn't have to manually copy it down and editing is so much easier because we just edit and it automatically spills the new results into the cells we come to the bottom alt equals and look at that f9 pound or hashtag that little symbol right there is called a spilled range operator and what it means is i'm going to get everything that spills from f9 because remember the formula only lives in f9 now we hit enter highlight control 1 currency click ok alright so we saw one two different ways to accomplish calculating tax amounts and then adding now let's see an excel table formula now table formulas can be created when we have an excel table now notice there's no context sensitive ribbon tabs but when i click inside this table sure enough the table tab comes up and if i click table design over to properties this table right here has the name tax table now that name tax table for the whole table and the field name sales and tax amount those names are used in formulas rather than cell references and as we learned last video when we used an excel table and a pivot table the reason that we would use a table for either pivot tables or for formulas is because when we add new records to the bottom of a table anything pointing to the table whether it's a pivot table power query or in our case we're going to have formulas pointing to the table those objects will see the new records that we add now there's another advantage also the formula that we will create will be automatically copied down not spilled but automatically copied down by the table feature and if we add new records any formula we create in a column or at the bottom of the table or any external formulas pointing towards the table we'll all update to reflect the new data all right let's see how to do this equals and with table formulas you have to use relative and locked or absolute cell references but watch what happens when i click in a cell in the same row as the formula because we're referring to a field the sales field in square brackets it shows sales because we want just the cell in this row you use the implicit intersection operator the at symbol now for us we want to think of this as a relative cell reference now we need to multiply times the tax rate and this tax rate is outside of the table so when i click it puts a cell reference in and although the table feature will automatically copy our formula down we have to remember to lock the cell reference with f4 and when we hit enter it's not spilling it's automatically copying and you can see in every single cell in this calculated field in the table the formula is actually there and look at that the formula is the same in every row that's where the at symbol comes in because it says hey i'm going to get whatever sales amount is in this row all right so we did have to use relative and locked cell references but we didn't have to copy and similar to the dynamics build array formula editing is easy i simply f2 after the equal sign i type round tab click at the end with my i-beam cursor comma two close parenthesis and when i hit enter the formula is automatically copied down now let's come to the bottom and here's another amazing thing about an excel table there's a built-in feature that will calculate various aggregate calculations so you can click in the cell use the drop down and i want to say sum and sure enough there it is now if you hit f2 it actually uses the subtotal function and if you want to learn about the subtotal you can click inside to bring up the screen tip and all function screen tips have a hyperlink if i click subtotal it opens up the task pane for help and sure enough you could see there's a list of functions and the numbers if we use 109 it will ignore hidden rows if i use 9 it sees the hidden rows but we don't really need to worry about that we just do some at the bottom later when we add records this is automatically pushed down now we have a total here if we come over here and we want the tax total off to the side i can use alt equals and another advantage of using excel tables instead of clicking and dragging it'll put the table name and in square brackets the field name instead of doing that i'm going to click escape alt equals especially with large tables this is all you have to do when you see that black downward pointing arrow you click at the top of the field name and sure enough it puts the table name and in square brackets the field name and that is how you reference an entire column in an excel table and enter now last video we saw how to add new records to an excel table we simply copied and pasted a bunch of new records but let's test this i want to see if all the different formulas will update let's just add one new record and here's how you do it you click in the last cell in the last column and when you hit tab a new record is added the formula is automatically copied down and when i type 2.99 tab the new record is incorporated into the table the formula is copied down that formula and this one all update if i look up here that formula is just looking at the table object and in specific it's looking at the tax amount column so if we add or take away records that formula updates now i'm going to undo that with control z control z three times now here's some notes about each one of these formulas for the single input output formulas you use those if you have to send a solution to someone without microsoft 365 or without the ability to spill formulas for the dynamic spilled array formulas the advantages are you don't need to lock cell references you don't need to manually copy and editing a formula is faster and easier than our siof formulas and the excel table formulas you use when you will add new records to your table you still must lock the cell references but formulas are automatically copied down and of course edited formulas are also copied down so the different types of formulas well these are aggregate formulas taking a bunch of numbers and giving one answer single input output formulas dynamic spilled arrays and then the fourth one is a scalar array formula and then finally the excel table formula now we haven't looked at this but let's take a look now and here's the simple rule if you need to see each individual amount and then add you got to do it this way you spill the results and then add but what if you had this column and you do not care about the individual amounts you just want the total tax amount well we can do that of course and watch we'll build it as a spilled array and then aggregate it into a scalar array formula i'm going to type round and let me show you arrow tricks right we know that we can get cell references with your arrows but in a situation like this i need to highlight the range so now i hold shift and then down arrow then multiplication symbol up up left comma two close parentheses when i hit enter that spills but that's not what i want so f2 i'm just going to put that resultant array into the sum function it's still an array formula and in fact there are two separate array operations in this one formula if we select that and hit the f9 key you could see that's the multiplication operation it generated an array of results ctrl z but here's the second array operation the round function also f9 delivers an array of values so ctrl z those two array operations are consumed by the sum function and we've achieved our goal total tax calculation without the individual amounts now by learning the five different types of worksheet formulas we can use this knowledge as our basis for building worksheet models now we want to talk about style formatting before we actually build some models i want to start on the sheet style formatting answer now there's basically two schools of thought when it comes to style formatting the minimalism school of style formatting and the non-minimalism school of style formatting i tend to fall into this group here but we're going to look at some important style formatting tricks that'll help us when we build worksheet models now let's go over to the sheet style format now on this worksheet i actually stole the finished model we're going to create in our next example and have it listed multiple times so we can learn some style formatting tricks now style formatting is all formatting for cells that is not number formatting things like fill color font color borders indents and more everything up in font alignment not this group and over in styles now i'm defining style formatting as manually adding various cell formatting styles actually lets you define a group of styles and reuse it but we're not going to cover that topic now the true minimalist school wouldn't add any formatting they say hey look why do you think they invented those default gray lines that's sufficient but even in the minimalist school they might do things like bowl the labels at the top so i'm going to select those cells and use control b to add bold now over on the left we have the labels for our budget model that we're going to build next and we have a bunch of expenses and total expenses so for this group of expenses we can add an indent home ribbon tab alignment indent another formatting element that a minimalist might add is this line is for total expenses so we can add a line to emphasize that this row is making the total expense calculation we want to select the row use control 1 to open up format cells then over to border this is where we have full control over borders now the order to this dialog box matters you have to select your line first we're going to select default then second you select your color then you draw your line now if you already have borders and you want to remove them all select none outline puts a border just around the outside not the vertical or horizontal interior lines and inside does just vertical and horizontal interior lines now we want a line just at the top to indicate that the calculation is total expense so we can use the border button it puts a line at the top when we click ok we can see our line now we want to do the same thing for net income a line at the top but we also want a double line at the bottom meaning this is the bottom line calculation control 1 we're going to select our line our color and then instead of using the button you can draw it yourself then we come over select the line the color and draw the double line border at the bottom click ok and we're done now one thing that is missing up here is number formatting it would be nice to know what the unit is and see commas now we're going to see three great options when you want to indicate unit and show commas but first i've got to show you a great trick i want to copy just the formatting that i applied here and then apply it down in three different locations to do that we select the range and up in home clipboard there's the format painter if you click that it copies just the formatting not the content we can also get to that button by right clicking the mini toolbar has all sorts of great formatting options including at the end format painter now if i click it once i get to apply the copied formatting one time but if i double click it which is what i'm going to do double click we can see the paint brush attached to the cursor and very carefully i don't want to click too high but right in the correct upper left corner i'm going to click that applies just the formatting scroll down click scroll down not there right here click now to turn off the paintbrush click escape now let's scroll up and we want to talk about accounting number formatting let's select the entire range with numbers control 1 and in the number tab we're going to select accounting and accept the defaults click ok now for this column here when we apply the accounting number formatting the appearance is too wide for the column so we have to change the width of the column i actually want to change all five columns simultaneously to the same column width so i'm going to select c drag to g and then between c and d or between any two columns i'm going to click and drag just a little bit that fits each column to the same width now accounting number formatting always puts the dollar sign fixed on the left accountants like everything to be lined up negative numbers appear in parentheses zeros appear as dashes those are both conventions from accounting now another trait of accounting number formatting is there's a space right there which we will not see when we use currency or number formatting that space is programmed in to accommodate parentheses for negative numbers so that all the decimals line up now if you're going to show your monetary unit like this that's a lot of dollar signs and it makes everything cluttered so a convention from accounting is keep the first row and any calculation rows but the rest of these we can remove the dollar signs control 1 accounting symbol none click ok and so that's looking pretty good for accounting number formatting now currency will select control 1 currency one difference between currency and accounting is you get to choose how to display your negative number now currency will line all the decimals up too as long as you don't apply different negative number formats within the range we're not going to do that we'll accept the top click ok the other main difference with currency is that it doesn't have a fixed dollar sign on the outside it's floating with the width of the number still another way to do this is not to use dollar signs at all but indicate in the label somewhere what the unit is in this case we can highlight control 1 use number select a comma separator click ok and that looks much less cluttered now one last thing notice that there is no space for number or currency number formatting but with accounting you see the space now the second school of style formatting is the non-minimalist that's where we add some color to our model to spice things up now i'm going to select this right click format painter just once click on amount i want to add borders to everything so i'm going to right click and on the mini toolbar all borders now let's add some fill and font to the labels i'll select these labels at the top and then using my control key i can select a range off to the side these are called non-contiguous ranges right click on the mini toolbar we're going to fill the cells with dark blue and font what you don't want to do is pick a font that's too close in value that makes it hard to read so we're going to select white font now let's select the total expense row control 1 and we'll do medium border at the top click ok net income line control 1 medium border at the top double line at the bottom click ok now when we build this in just a moment as a model the only numbers that are not formulas are those so i'm going to highlight the totals using my control key and then right click fill that green there and then on the inside that'll be one formula hold ctrl this is a different formula right click and a slightly different green and one final touch up in the left corner fill yellow and there we have our non-minimalist color filled sales and expense budget all right now we have style formatting and the different types of formulas under our belt let's go build some models now here are some important worksheet formula notes that i always think about as i'm creating worksheet models those are in the pdf notes also let's scroll down and look at example one example one you are required to make a sales and expense budget the estimated sales for january through april are those numbers the expenses a percentage of sales are as follows we have to create amounts for cost of goods sold operating expense administrative expense and other expense now what does it mean to have an expense as a percentage of sales well for cost of goods sold 37.5 percent means for every one dollar in that january sales amount 37.5 pennies will go to cost of goods sold so to get the total cost of goods sold for january we simply multiply those two amounts all right so our checklist for building a model excel's golden rule is always the first thing we have to get all of this into the spreadsheet not just the numbers but the labels too now this one takes some thinking about how you're going to set it up and you might try one thing or another i'm going to start off by listing the expense percentages now i added the labels for our percentages and i added style formatting red at the top that's something i use for my models to visually indicate that the numbers i'm going to put here are numbers i can change for the budget we're going to create below now when we're entering percentages you can enter them in as decimals or percentages but if you're going to enter them in as percentages it's usually faster to highlight and pre-format control-1 percentage two decimals click ok and notice that in the number group in the home tab we can see the percentage number formatting has been applied i click on the top cell and when i type a digit that percentage symbol pops up and lets you know that this cell has been pre-formatted 37.5 enter 25 notice i only have to type 2 characters and enter 12.5 enter 7.5 and enter now if these are expenses and they're going to be matched up against sales and these are all of the expenses then if we total these up if we get less than 100 we're we're gonna have a profit more than a hundred percent will have a loss alt equals and enter so 82.5 percent that number means for every one dollar into the cash register at this business 82.5 of those pennies goes to expenses the difference between 100 and that is 17.5 percent and that's left over for profit now documenting the model well we did that when we followed excel's golden rule and listed labels for all the numbers we could use formula text to show the formulas but i'm not going to do that in this model we will do that in the next model and we could enter the sales amounts up here but i'm actually going to build them right into our budget down below so we'll start amounts in dollar so i'm not going to use number formatting to display a dollar sign because it's too cluttered tab and i need january to february so i type j a n control enter because my next task is to copy this i point to the fill handle and because excel is so awesome when i click and drag it knows to increment each month over here we want total enter luckily it sends me back this is going to be the row for sales tab i'll type 5 5000 tab 6750 tab enter each one of the numbers tab now i'm going to wait to the end to add number formatting but now we need our list of expenses and because i already entered in cost of goods sold to other expenses up here i'm going to go get them with a formula and i'm going to spill the results since there's one two three four expenses when i enter that formula and enter it lists the expenses now once we've listed our expenses we can get busy calculating the actual expense amounts for each month now this is the classic budget formula conundrum in excel now there are three different old school formula methods that people take to approach this problem because what's going to happen for this entire cost of goods sold row we're going to have to use that 37.5 percent and multiply it by each one of the sales amounts for each month then when we get down to the operating and expense row we have to take all four sales and multiply them by the 25 percent looking at it from a different perspective for this whole column i need to take that 5000 and multiply it by each one of these expense percentages so what i'm going to do is i'm going to hit pause and show you the three old school methods and that way when i show you the new way that we do it in microsoft 365 excel it'll blow your mind how much easier it is method number one is when someone manually typed in not considering the different types of cell references every formula in this cell it's looking at 5000 times 25 which is correct but over here this person manually recreated each one of these that's 32 formulas now bill mrexcel jelen a fellow microsoft excel mvp he has a funny story about how his boss created budgets like this but he was such a mean boss that mrexcel saw he did it all by hand and he knew he couldn't show the boss the easier way the second method is to lock the sail since it's used throughout the column and then you create only four formulas now the sad news is the textbook that i used many years ago at highline college used to teach this method now the third method is by far the best of the old school methods it does involve using mixed cell references that cell reference is locked only on the row this is locked only on the column so you can enter it copy it down in two steps one and then check the diagonally furthest one away with f2 and sure enough it got it right but when i delete this and show you the new method you're not going to believe it equals well i'm going to highlight all four sales amounts and we're going to multiply by all four percentages because these are oriented the same direction as the expense labels over here this array formula will take the first element multiply it by all four the second one multiply it by all four and do that all the way down when i enter that's it one formula didn't have to manually copy it and by all means we didn't have to use mixed cell references in fact this is one of the best examples of how microsoft 365 excel changes everything into the future of how we use worksheet formulas now the next step is to consider whether we have to use round remember there's three rules well do we have to round in the first place well yeah this is money do we have extraneous decimals well i can see from the result we do but guess what up here you could have already guessed there's an extraneous decimal there and the third and most important thing are we going to use the formula results in other formulas well yes we are we're going to total and calculate net income so we have to round and again we're editing but we only have to edit the top left corner for a dynamic spilled array we round it to the penny hit enter and the edited results spill now let's calculate total expenses tab and we're going to add so alt equals and we have not bumped into this problem before but this is an example of why you always have to verify that the function guessed correctly and it did not you absolutely cannot add sales with the expenses you want only the expenses so we will redirect now this is an aggregate formula and by definition even though we need formulas below each column we can't directly spill an aggregate formula control enter and we copy it to the side click in the last cell and hit f2 it's looking at the correct range now let's calculate what's called net income the synonyms for this are profit or earnings tab and businesses just take all of their sales or all of their revenues and subtract all of their expenses to get what's left over so equals and this is not going to be an aggregate calculation we'll use the subtraction operator so we'll take all four sales amounts subtract all four total expenses each corresponding number will be used and when i hit enter the results spill to the right now for each line we want a total total sales a total for each expense total for the overall expenses and overall net income now this is an aggregate formula so we can alt equals it guesses right so we control enter and because i want to copy this all the way down to the last number on the left i can point to the fill handle and when i see my angry rabbit double click and send it down i go to the last cell and hit f2 now i got to show you a slightly different way to add totals and it involves highlighting a range like this or simply like this when you alt equals well it is a little bit faster but i always get nervous because it didn't give me the opportunity to verify that it grabbed the right numbers if i do it this way i usually verify with f2 at the top and f2 at the bottom now the last item i have in our checklist is default alignments i am leaving text aligned to the left and all my numbers to the right if this is a finished report you can do something different but i like my default alignment because it gives me visual cues about what type of data we have now the last thing is style formatting and you can format this how you want i'm totally going to cheat i'm going to go over to style formatting highlight right click mini toolbar paintbrush come over to my model and click and now i am done and here's the moment of truth we built a model we have our checklist we're using this in a meeting and of course the boss says well cost of goods solds is actually 43.5 so you change it and when you hit enter everything updates all right so that's example one let's look at building model number two example two gel boomerangs manufacturers boomerangs and calculates the price of the boomerang by adding a 72 percent markup on the cost of the product cost in this case equals how much it costs to produce the boomerang all the wood the paint the packaging and so on if the quad boomerang cost 13.55 to manufacture that's the cost what is the price build an xl model well we need to get the details into the cells now i entered the labels the formula inputs added style formatting and kept the default alignment and for percentage markup on cost i left it as a decimal but by all means control one if you want to show it as a percentage that's okay now i'm going to control z either way as a percentage or a decimal the meaning of percent markup on cost is how many pennies to add to each dollar of cost to get the price that means we can simply multiply this percentage markup on cost times our cost and that will give us the markup in dollars so let's make that calculation equal sign there's the cost times percent markup on cost and enter now we have the markup in dollars we can add to cost to get our price now we do need to round this but i'm going to leave that unrounded for the time being and round it down below so equals round and we'll do it to the penny and enter so 9.76 our price will be equals get the cost plus our rounded markup and when we hit enter 23.31 cents now from our checklist we use the round function appropriately and i do want to document the model because we're going to have a couple different formulas for price so in cell f64 it looks like we're going to have six different formulas so i'll create a single formula text function that spills the results equals if n a and then in values formula text and we could use our mouse or our arrow keys i arrowed over and now i'm going to shift and arrow down close parentheses and then if there is no formula we'll show nothing using double quote double quote close parentheses and that will spill now sometimes you definitely want to do the two-step method markup and then the final price maybe you want to know exactly how much the markup is but other situations you don't really care you just want a direct formula that goes from cost to price well that's easy enough we'll just combine the two steps equals well there's the cost and we have to add the markup well the formula we used was round cost times percent markup on cost and we rounded it to the penny when we hit enter that formula will work now there's an even easier formula equals round and what i really want to do is i just want to add one dollar to this .72 that way when we multiply 1.72 times this it'll give us the full amount plus the extra markup now we're going to have to force the plus first so in parentheses 1 plus that .72 close times there's the cost comma 2 close parentheses and this formula will work also now the business decided that 0.72 was a percent markup on cost but we could check that amount because here's the formula that compares the amount of the markup to using division the cost it better be 0.72 and the reason that it's not is because when we did the initial calculation there were some extra decimals so if we really want to check and get the exact .72 the formula is actually the unrounded amount and then we compare that to our cost and enter all right so we've completed our model we've checked our checklist and now if the quad price is 15.25 no problem everything updates if the percent markup is now 0.82 our model is working control z z now let's scroll down to our next example example three example number three we need to calculate the total number of days and the total amount of time it took to complete a project if the project started on 7 13 2022 ended on 7 27 2022 and the team worked each day from 6 a.m to 1 30 p.m we need to build a model now before we can build a model like this we have to learn about date and time number formatting and date and time formulas so if we scroll up here are some date and time number formatting and formula notes now the first thing is we have to figure out the format for valid dates and times based on your regional settings so we'll use the window key and then type regional i tap window type reg i see regional settings so i click this will tell you how you're allowed on your computer to enter dates and times now this may come as a shock to some of you but i built this table here to show you what actually ends up in the cell when you type a date or a time if you look up here under date number formatting there are serial numbers which are the number of days since december 31st 1899. yes that's right the very first day that's possible in excel is one one nineteen hundred and under that date number formatting is the number one one two nineteen hundred two one thirty one nineteen ninety nine thirty six thousand one hundred ninety one now if you try to type in a date that's before one one nineteen hundred it's text it's not a number now the genius behind this is you're allowed to take two dates and subtract them and that'll tell you how many days there are between two dates now time is a little bit trickier under time number formatting there's a serial number that represents the proportion of one 24-hour day yeah if you type in 6am like this excel actually divides it by 24. so all time serial numbers are numbers between 0 and 1 where for example 6 a.m if you enter that excel behind the scenes takes 6 hours divided by 24 and underneath it stores 0.25 so yes 12 a.m is zero six am is .25 and so on now this means that when we're trying to figure out or convert time values to number of hours like we're going to need to do we need whole numbers so the formula will be whatever the end time is minus the start time and we'll have to multiply it by 24. and the reason is simple if excel in the first place takes the number you type in and divides by 24 to get it back up to an integer you have to multiply all right let's scroll down all right i've entered the labels in some style formatting and we want to enter the dates and times and pay close attention to number formatting and also we'll check to see what is actually in the cell if i type the start date 7 slash 13 slash 2022 when i control enter the visual cue immediately tells me that's not a date not a number also i see that the general number formatting is still applied but as soon as i remove the 133 days when i control enter the default alignment lets me know it's a number and look at that when you type a date a date number formatting is applied now if you look up to the formula bar you cannot see that serial number it just shows 7 13 20 22 but if we go backwards just to prove to ourself that those numbers are really there and apply general number formatting look at that 44 750 now i'm going to control z enter the end date is 7 27 slash and i can put 22 and enter now to calculate the number of days you always take later date minus earlier date so equals up arrow minus up arrow up arrow and when i hit enter that's why they put serial numbers under dates we're doing date math so 14 days for the total project now times 6 a.m and when you enter time it's hour colon minutes colon seconds now if you don't have seconds you can leave those out you have to type a space and then am or pm i'm going to type am and control enter well sure enough you have to either omit am pm to put in military time or you have to include that space we can see general up here but as soon as i add a space control enter the default alignment to the right tells me it's a time which means it's a number and up here i see custom that's a certain type of time number formatting now i'm going to come down and type 1 colon 30 space pm and now when i hit enter i have two times a later time and an earlier time now just to prove to ourselves that those decimals between zero and one are really there i'm going to highlight those two and apply general and sure enough excel took those integer hours we put in divided by 24 control z now here's what a lot of people do when they're first trying to do time they're like well this is sort of like dates right later time minus earlier time and i should have showed you but you could see up in the gray the general was applied when i control enter it actually stole the number formatting and applied it and applied that custom time now it looks correct because that's seven and a half hours that's the correct number of hours we need but if we remove the general number formatting if we want seven and a half times fourteen to get total hours that's just not gonna work so control z f2 the way you calculate time in integer hours is you force the subtraction first and then because excel divided by 24 we're going to bump it back up by multiplying by 24. control enter whoa the reason it's showing that is because there's some number bigger than one now this is where the keyboard for the general number formatting is so important control shift tilde now we have what we want total hours for the whole project 14 days times 7.5 and enter 105 hours now the boss says well wait a second we start in on the 10th so you're like no problem i change the date and everything updates control-z now i am going to document this equals if n a formula text and i'm lazy i know that there's two formula inputs there but i'm going to close parentheses comma double quote just like we did above and when i hit enter it'll just skip over those two now here's our checklist we followed everything and we didn't use round here especially on the internal time calculation since all those messy decimals are really important all right now we want to go build our fourth model we're going to go over to model ex4 all right here's example four calculate the amount of the profit from the biggest sale for each product the profit margin is 17.5 create a dynamic worksheet model sort the report a to z by product so we're calculating profit and that's based on that percentage but the profit is based on the biggest sale for each product so the first thing we're going to have to do is from this column get a unique list of products then based on those products we'll have to look through this column get the biggest sale for each product and then calculate the profit all right i entered some details added some style formatting and even some number formatting now we've already seen the power of dynamic spilled array formulas but in this example we're going to see something brand new in microsoft 365. our goal is to get a unique list of products and there's a brand new dynamic spilled array function called unique in array we simply highlight the entire range this is an excel table so i click at the top close parentheses and when i hit enter it spills a unique list not only that but the reason that spilled arrays are called dynamic is because if i added a new product over here the sunshine boomerang instantly the formula on that top cell spills a dynamic unique list now i'm going to control z in the top cell i'm going to hit f2 because one of the goals is to sort the product names and here's another dynamic array function called sort so oftentimes you use sort and unique together we're nesting them close parentheses and enter so if i add the z z model instantly that appears at the bottom a a that appears at the top control z control z now the next task is for each individual product we need to get the biggest sale from the sale column now there's a great function called max and it returns the largest value from a set of values but if we use max on this column here it'll pick out just the single biggest or maximum value what we really want is we want the max value from this column if this field right here contains aspen if this field right here contains carlota so if i down arrow the max ifs returns the maximum value given a set of conditions or criteria so for this row max ifs will use the aspen condition and only match records where there's an aspen then it will go over and from the aspen sales pick the biggest value so we hit tab for our single condition there's three arguments max range criteria range and then the individual condition or criteria max range that's the full sales column i type a comma criteria range that's the full range with all the product names so i select the column comma and then the criteria that's the individual condition now if i select just aspen of course max ifs will deliver just one answer but i really want this to spill so i highlight the dynamic list of product names and of course since sort and unique are spilling from that top cell g9 g9 is listed with the spill range operator that means if this list expands or contracts so will max ifs so i close parentheses and when i hit enter the results spill down and of course it's dynamic if i change this to sunshine and hit enter that is absolutely amazing this formula update and because max if's is connected to that it also updates now i'm going to control z now we can calculate the profit for each one of these biggest sales equal sign left arrow and i can just type the pound times and then i'll up arrow to get that and i don't have to lock this when i hit enter it spills the profit now we have lots of extraneous decimals and this is money but guess what we're never going to use this in any other subsequent formula so we don't really have to round we can just use number formatting now i'm going to select the top two cells and at least a few cells below because i'm anticipating that some records may expand downwards control 1 number comma that's looking good click ok now documenting the model well below here let me show you a trick if n a formula text and i'm going to select just the top cell close parentheses comma double quote double quote close parentheses control enter and i'm going to copy it to the side i know it doesn't fit but then i'm going to use my move cursor to move it down and then move this down now here's the moment of truth we're going to scroll down and we have some new records now actually i want to add these records to the bottom of this excel table and watch our report instantly update so we're going to hide some rows i'm going to select row 91 and then click and drag up i'm not letting go of my click and i'm going to stop right on row 22 and then right click hide right so we can see between 21 and 91 those rows are hidden select one cell in the new data control asterisk to highlight control c to copy and below the excel table watch up here when i control v and just like that our model updates we have a sorted unique list of products the biggest sales for each and the profit and of course if the boss says well it's actually 12 percent and enter everything updates control z now as a final note i actually didn't add the green to the formula cells because these are expanding and contracting but we have documented the formula cells down here now those are the four models we're going to build but i want to look at two last models just to show you other possibilities so on example five this is a cash flow model from finance we have a number of inputs correctly labeled then we do some intermediate calculations and we have two different metrics irr and npv these help us determine whether or not to make an investment if you're interested in those kind of models i have a full playlist on finance at youtube example six this is a business analytics model a very complex one with lots of inputs and a simulation at the bottom this helps us to decide the risk associated with building a new product in a business i also have an analytics playlist with a bunch of videos if you want to check those out all right now that you've learned all about building worksheet formula models there are some homework problems some practice problems the blue one is where you do your work and complete the problem and then you can go look on the red sheet how i solve the problem all right we covered a lot in this video we covered modes of cell editing the five different types of formulas we talked about style formatting and then we built four worksheet models with worksheet formulas always considering number formatting style formatting always adhering to excel's golden rule documenting our formulas and using round appropriately all right this was an epic video on worksheet formulas and models next epic video will be all about data analysis alright we'll see you next video [Music] you

Channel: ExcelIsFun

Views: 53,703

Keywords: Highline College, Busn 218, Mike Girvin, excelisfun, Mike excelisfun Girvin, Microsoft 365 Excel, Free Class, Free Course, Formulas, Functions, Dynamic Spilled Array Formulas, Microsoft 365 Formulas, M365 Excel Video 3, MECS 3, Comprehensive Formula Lesson, Complete Formula Lesson, Excel Formula Class, Learn Excel Formulas, SORT function, UNIQUE function, SUMIFS, MAXIFS, Excel’s Golden Rule, excelisfun formulas functions, Building Models in Excel, Excel solutions

Id: AUYagm4ZA7M

Length: 71min 13sec (4273 seconds)

Published: Sun Jul 24 2022