Collaborate on Bilty Format in Excel for Quality Assurance with Ease Using airSlate SignNow

Bilty format in excel for Quality Assurance

it is my great pleasure today to introduce everybody to dilip shaw those that do not know dilla dillop has been in metrology for uh probably since what eight years old when did you start trying to measure things dylan when i was seven years old so i can really truly say i uh i got involved in metrology at seven years and uh that's a story for some other time dude that's another story so he's been in metrology for seven some years i've had the great pleasure of knowing dilip for well over a decade we've worked together we're good friends and we talked we were going to do a training for the week and we decided to postpone a week-long digital training class and we came up with hey what can we do that will benefit a lot of the people that are out there uh some time savers and other things and we came up with this class seems to be highly attended and we wanted to morehouse wanted to present this to everybody for free so philip is a help slabs cure their measurement headaches he is author of the metrology handbook and just general the award list is is very vast on all the awards and everything else that he's won over these years and i hope that everybody gets great value from this presentation with that i am going to mute my microphone and let the maestro take over okay good morning everybody and um i know you may be joining from different time zones and uh uh but uh here i'm in ohio on eastern time and it is morning for me but wherever you are uh good afternoon good evening whatever the case may be or maybe very very early morning um so excel is a a tool application that is clear to me because i grew up in the bc days and when i talk about bc days i mean before calculators and before computers and uh you know i grew up using slide rules and logarithmic tables and trig tables to do my calculations so when a tool like excel comes up it is near and dear to me and excel before excel there were other spreadsheets so there was the visit called that was the first spreadsheet and then there was lotus one two three quattro pro and now excel when excel started out excel was pretty bad um it didn't have a lot of things in there but as excel got better better and the nice thing is almost everybody has excel and then if you don't have a spreadsheet there are other like the google spreadsheet another other spreadsheet that pretty much mimic a lot of the things excel does and i started doing measurement uncertainty budgets in 1988 and i started using spreadsheets so back then i was using lotus one two three lotus one two three is now extinct but the other spreadsheet the quattro pro still exists i still have a copy and the the issue releases like excel two uh almost like every year uh quattro for 18 months or so so we thought we would put some quick shortcuts um things that we do can't do much in a one hour and as much as i think i'm good at excel i probably know 10 of excel but what that does is it that tells how powerful excel is so uh we will start with some very basic things in excel and then we will kind of go and see uh just logistics uh you have been muted so there's no background noise for everybody's comfort and if you have any questions please put them in the chat panel and we will answer a few at the end of this webinar in there okay so we're going to talk about some basic math statistical functions uh look at some charting capabilities in excel excel shortcuts and there's so many different shortcuts and there's some time saving things we can do instead of manual number crunching um then like i said in the bc days i could add 10 numbers in my head in very short time like within like 10 15 seconds i could add those numbers now if you see me doing that manually just adding or subtracting two numbers i'd be struggling because that's how much i've been spoiled by excel and and then the other thing is like anything else this was the whole thing in in the 70s when computer programming started getting popular this was the term gigo garbage in garbage out and that applies to anything um you do including excel uh fortunately or unfortunately computers the the ai the artificial intelligence hasn't been that that it's gonna do the thinking for you and when i think about the thinking i always think about the the terminator scenario where the computers get smart and they think all human beings are imperfect so they must be eliminated so that's a good thing that we can still do all the thinking and then we can let computers or applications like excel do the the sort of a tedious number crunching task and then that always applies and it is pretty true excel can do certain things but if you don't put thought to it on how we formulate the formulas or the functions we use it can come and give us an erroneous answer so we still have to do the thinking in that way all right so let's look at some shortcuts now i grew up like i said in the bc days and then i grew up with the the personal computer revolution and when the computer started we did have this nice little thing called the mouse and so if you had to do certain things which you can now do with a couple of clicks of a mouse you had all these shortcut keys and those shortcut keys still work and there are times where i will use that instead of the mouse clicks and so with excel uh we have some shortcut tricks sounds and the way it is you have a control key and another key the plus sign you don't have to press the plus sign but it says the control and the n key for example opens a new workbook or opens a spreadsheet control moves you to the home menu uh control p opens the print dialog box the control s saves the workbook and then the control c copy cut for control x and ctrl v for paste so a lot of times i will still use that and uh am i when i'm doing these things in my workshops uh people will say well how did you do that i just do the right click on the mouse or i go to the menu and i do the copy and i say yeah these are back from the dos days dos meaning disc operating system and that's what started uh you know originally before the the windows choreographic environment open and with the advent of the mouse and so on and those shortcuts still work so there's like two or three different ways you can get to the same place in excel 2 and i'm so glad that excel has still kept those other you know shortcut keys in there because sometimes it's easy for me to do those things so i just thought i would start with the basics and then excel has a sum formula but you could do the same thing with the alt equals and the whole idea in there is if you're summing a row of numbers uh or a column of numbers you would ideally have a one extra cell you would select and it will do the sum formula and then there are other variations of it and i follow a lot of excel threads in youtube and i just found another quick easy way uh to do the sum formula where you had blank uh rows or columns in between your data enabled to those actual sub sums between certain ranges and so on so there's a lot of different ways you could do that and i just learned that trick like a couple of weeks ago so remember i said i'll probably know how ten percent of excel and then i learned that and says oh it can do this too as well and a lot of times we have a lot of different excel windows and you could click through your mouse but the control tab will you know toggle through those different excel windows and uh and then control one gives you the format cells dialog and the format cells there's a lot of ways you can format the cells for different things like uh a number format a currency format date format those kind of things and it gives you that menu and one of my favorite shortcut shortcuts is the f4 key and f4 key repeats in action and it it kind of sprawls through all the different combinations you have and a lot of times when i'm doing a lot of calculations i want to reference a row or a column all the time and it will put if you're on that cell or not even the cell but that actual cell reference in a cell and if you put your uh cursor by that and go through the f4 it will toggle through all the combinations with the dollar sign uh and the dollar sign reference is to absolute reference and then if you want to copy that formula through a whole set of rows in a column and not want to do a relative reference because otherwise if you do that it automatically do the other calculations in there so um just some of the other things in there that are there and i see people are putting in other other uh references to uh with keys and what happens is i learn as much sometimes from um the other shortcuts people know and this becomes very useful and work right so um just kind of go through that and and i was talking about the old uh equals and i just kind of have a couple of screenshots where i can you can see i've left the column in a row empty and it and then when i do the alt equals it will go up and uh do the actual sum of the rows and columns excuse me um the f2 function basically lets you edit the cell and but you could also double click and it will do the same thing uh and help you audit edit the cell and a lot of times i teach a lot of measurement uncertainty our shops and we kind of do it where we kind of build the budgets and i'll be working on a cell and somebody says uh wait a minute what should you do on that cell what formula what references to cells you add and i can do the f2 key and it will show the formula but then there's other keys which will show the formula in all the cells so um that is a useful feature to have um one of the things is if you're using excel and creating templates and from in terms of the uh iso 1705 requirements if you create a template you're going to use it you have to validate it and if you're going to validate it or you may just want to keep a spreadsheet where instead of just the template with all the numbers you want to see what formulas you use for if some reason the spreadsheet gets corrupted or you gotta create it uh you could save it with this all the formula showing not just the single one and that will kind of give you control and until they assign and it will give you and display all the formulas and then you could take a save it as a spreadsheet like that and then it will help you with the validation efforts and those kind of things that you have and of course f7 is that spell check and and and you could use the spell check by doing a full uh the tab on the spreadsheet or you could highlight a number of cells and do a f of f7 and uh you could even uh spell check by grouping multiple uh tabs uh in there so those are a lot of the other things in here and i see that we have a lot of things that people are sharing too so that that is great in there but uh but these are some of the things yeah so andrew made a comment who needs spell check but you'll be surprised what sometimes you spell in there and sometimes that can be pretty embarrassing so i i do a lot of like internal audits for labs and i will do my internal audit so i have a spreadsheet template for that and i always do a spell check because i'd say oh my god if i just sent that without spell check there's all kinds of things that'll be pretty embarrassing and i don't didn't mean it to be that way but you'd be surprised what it can do with the spell check now it's not perfect but and we work in technical areas and whatever is in the dictionary you may not have all the terms like it doesn't have things like repeatability reproducibility in there but you know what you could add those spellings in your dictionary and then it will do that check unfortunately there is a way to import the dictionary in the other spreadsheet on your or other excel in other computers so i haven't done that yet um but there's a way to do that so you have the same spell uh your additional dictionary words you may have added to that and i have like 15 computers and depending on which one i use that would be useful and there's a way to do that in excel too and just kind of going still with the shortcut keys you have the control page up and control page down and that lets you scroll between the different uh worksheets in excel now the thing with page up page down keys is on my laptop key keyboard those page up page down keys are shared with other keys and i'm using a surface tablet and that's using it with my function keys so you you want to make sure that you have it toggled properly in that way and now this control plus is view formulas and this symbol here the tilde is the shift key and that's the one i was talking about but you don't have to press the shift key you just do a control but the tilde key is sometimes shared with other keys because it's a shift for a function and that's the one i was telling you about and a lot of times we look at data which is arranged in rows but we may want to convert it to columns or vice versa and then i can use my copy paste special and transpose and it will convert rows to columns and columns to rows and excel actually has a transpose function as well and that gives you another way to do this things in there and and then the other thing uh is auto filling a series of formulas and you would normally like click on the cell and in the bottom uh right corner you will have a sort of a plus symbol you drag it down or left or right and it will go and fill the the cells and there's a little bit of trick to that is if you wanted to increase it by a set number uh you may type in 1 1.1 and then select those two keys with that plus and then drag it down and that will increment by incremented by 0.1 and vice versa the nice thing about this is um it not only works with numbers but [Music] it is it will also do labels like you could do monday tuesday um wednesday and drag it down it will like fill up or by month and things like that so it uh it works in that way and then this slide kind of shows you what i was telling you about uh and how that does it in there so uh so those are like some of the things that are nice shortcuts or you may just want to like uh you know auto number a set of uh just labels and you could do just the the number value in there and instead of typing in you know one two three four five six and so on or 1.1 1.2 and so on so uh those are some of the things you could do now the other thing is like sometimes i want to put a lot of text in my cells but i want to say like this is and then one two three and so on now normally in a cell if i want to put all of those things in there i'm gonna have to like add the extra spaces until it sprawls down to the next part and so on and uh that is kind of pain in the neck because then you you share that spreadsheet with somebody and the way their their spreadsheet is set up it will not line up properly so you could actually force a carriage return within the cell in the labels and then you could uh force uh the carriage return by pressing the alt enter and and then it will keep that alt enter and you could actually have bullet points and things like that as well so uh so those are uh nice things in here and i talked about like formatting a cell to a certain format a date format a number format uh so i can format one cell but then i want to format those other cells to uh the same format excel has a format painter and if you click on the format painter button or in that copy and format paint it's got another option to do that you can then format the cells and you could do like one cell to another or you have a whole bunch of cells if you and they don't have to be in a continuous [Music] order uh if you double click on that format painter it looks like a paintbrush symbol uh you can double click double click or just double click once on that and then go and click the appropriate cells you want to format the same way and it will go and format it and the only thing you have to do is once you're done you're going to press the escape key or the next uh cell you you select it will go and format it inadvertently and that's the only other thing like you have to worry about but uh since i teach measurement uncertainty and i use excel there are certain number formats i like to use and it's much easier for me to do it this way and for us to display looking at metrology there's a lot of things that happens and and we want to format it the same way so i use the format painter quite a bit you know for that purpose and the other thing is [Music] in in my spreadsheet i have a lot of different tabs and again the tabs by default are just the regular you know white with the black text but it makes it a little bit easier if i had the different color tabs it's a lot easier to distinguish hey dylan we lost your screen for a second to share okay yeah and i i saw something happen um in there so um this was the screen i was working with but everybody uh can you see the screen now no all right so let me let me do this i actually lost the control so head are still there heather needs to give me the control back because there's no way i can click and uh okay well while heather's going to give you the control i'll go i'll go over here hopefully heather can give you the control deal if you can drive a little bit over here on the share screen i can try to give you the screen some of the things that you were talking about earlier you know people were the format painter right if i like something um yeah so to say if i liked if i was all right so now i have the okay yeah you've got it back i'll stop my share here i'll i'll show them real quick what you were talking about with the painter uh while we're doing that yeah but um people should like yeah if i like these i could go to format painter here look at all this and then copy see what i did i copied it over to white if i like this format painter i come over here and now they're all gray but that being said here i'm gonna all right so uh let's hopefully you can come back on yeah let me get back uh to the screen here yeah there was like a blip and then it uh and i saw something happen where i lost control right so now you should be able to see my screen yep yeah very badly how to do what you're showing yeah and uh if you need to like i can i can do the same thing i do have an excel uh spreadsheet and um if we need to we can like i can jump back and forth and show you that but um what i was saying was it's kind of nice to like have uh the different uh tabs colored and actually i am going to like a switch and here i have a spreadsheet and you can see and you can see i've got all the different uh tabs different color for different things so um that's one nice thing i can do in there by just and it's easy to kind of distinguish and i could have a special color code for different things in there but uh i could do that with the the default colors and then there's more colors and like i said i grew up in the bc days and then i grew up with earlier versions of excel and if you had um before 2007 then you had just like a six colors and now you have probably a combination of some one million colors or so with some special color and the different shading of those colors in there so you do have um quite a bit of color that you could color parting in there but it just makes it easy to like distinguish in there and the other thing is um sometimes you want to combine either the text or other labels or anything like that and you could use this ampersand symbol to combine the references and it will combine the [Music] the text or whatever value you have in from others into a single cell and then you could format it by putting you know space between quotation marks and other things and one of the things i like to do is like you know we look at like tur formula and we calculate four to one and then this four two one the colon that you do in the calculation it will just show like four point zero two but four two one if i wanted to do this i can use that to uh add that to my uh uh cell and it will show the four to one uh tur test uncertainty ratio or things like that uh in there and there's many times i have used this uh just to kind of show uh in the presentation in the excel because otherwise excel is really a number cruncher and nothing else does that so it gives us opportunities now uh there are pre-formatted uh formats in the excel and i said you got number formats you got currency formats you've got date formats and different ones but there's also custom formats you could do and there's a bunch of custom formats already defined for you you can take them you could modify them and create your own format as well and uh some of these i've shown you here how to do uh in here but uh if we go to like excel one of my custom format is i'm very picky about uh engineering notation now excel has the scientific notation format already predefined engineering that don't have it predefined but if you go to the custom and follow those keys and it will give you that format and then when it gives you that format it only has a one decimal format and if i wanted to create a one with two decimals then i can just go zero zero e plus zero and i would have created my another format so i can take already a predefined custom format and then create one the other things like you could have a phone number so you would have an area code and then you know your three digits and four digits in the us or you may want to put the plus one for the the country code for us or anything like that and you could create formats like that uh or other there's a lot of other formats you could create but it's kind of interesting that your ten dollar scientific calculator has keys for scientific and engineering notation or the s i notation a lot of times they'll call this the s-i notation as well but since i teach a lot of measurement uncertainty workshops and in uncertainty values could be small and i like to show them in engineering format because then i can relate to them in the magnitude of the number whether it's 1000 times bigger smaller million times bigger or smaller and anything like that so uh so those are the kind of things that are useful and worthwhile uh to to do with the engineering format in there and remember again garbage in garbage out so the more we do these things up front and make our spreadsheets uh more foolproof the better it is and show it in the proper format and it is a lot useful uh in in that manner in here and then the other thing is uh sometimes you copy a formula and the formula has references to different cells and uh if you don't if you copy that formula to another cell it is going to take those cell references and depending on whether they're relative or absolute references it may reference a completely different cell but you just want to just copy the values and then you could do the same thing copy paste special and base values or you could go through this all alt e s v enter and it would do the same thing um so there's other shortcuts like i could do it by my mouse clicks or i can like have a combination of those keys and yeah the combination of those keys that's a lot of case to press at the same time and i'm not very uh you know quick with those fingers and uh so that's that's one thing and then sometimes i just want to copy the formula instead of values and that i could do the same thing so the paste special function is kind of useful in that way excuse me and the other thing is sometimes you have labels or rows of labels or columns that when you scroll your spreadsheet which could be pretty huge from column to pollen or rows to rows and and then you don't want to go back and say well what what is that row and you kind of go back so you do you can freeze rows and columns or a combination of rows and columns so those stay frozen and the rest of the spreadsheet you know scrolls up down right left and so on and you could do it in the view tab and then freeze panes and it will give you those options to do that so those are all like useful things from the presentation aspect of the spreadsheet and the way you calculate formulas and those gives you the the different shortcuts through different ways either using your keys or mouse clicks or the excel menu in there now um the other thing i i have uh an issue or is when i teach my classes a lot of labs have older versions of excel when i say older versions of excel um anything before 2010 now excel started getting the transformation to the ribbon menu starting with the 2007 version and then 2007 it added enhanced functions uh to what legacy functions that were out there and so on it's just like that color scheme i was saying before 2007 excel had a palette of like 15 colors and so compatibility wise you have to be careful that not all of these things work in all the versions of excel and [Music] sometimes some things may not work but what i've tried to do is most of the things will work but if it doesn't work and if you're using an older version of excel then that may not work and what i found is sometimes laboratories will have older versions of excel and they have it for a lot of reasons the the application they've written the macros and everything is in there they will not work in the newer versions of excel so they will keep using the older legacy versions of excel so you do need to keep that in mind as you go through some of these things in here but i try to use if if there's a legacy function available i try to use that because i i will have people in my classes they'll have you know i don't know what versions they have if i use the legacy functions they will work in the newer versions of excel but not the other way around and then i just kind of mentioned that in here now the other thing is um you know sometimes laboratories will print the calibration or test certificate the report in excel and excel has a way you could you know set your print area formatted but there are times where you you may not want to print everything from top down you may have something in the middle that you may not want to print uh and the the thing with the the newer version of excel is you could add to the print area so you can define your first print area and then you could add omitting those areas in the middle or so and then you could format a really nice you know report that you want to print and you could preview print preview and set it up and do that and then if you wanted to clear an area yeah you can obviously print uh clear the print area as well so that is uh one thing that you could do and you could do a pretty good formatted print area in excel so uh so that is another thing um you could do and then obviously you have things you could set where your page breaks so the data is um the the technical technical term is like you don't want uh the data like from one page to another where you're in the middle of a paragraph uh or so and then it continues and in the technical terms they call it widows and orphans so you could uh you know set the your page breaks property properly so you don't have widows and orphans and and that's a term from word processing i guess so uh that you could do in word but you could do similar things in excel as well so and then a part of the print is you may want just like we froze the panes uh we could do the same thing we want the rows to repeat at the top and the rows to repeat at the bottom in the same way and then we could have a pretty contiguous report so we could set up this now this page setup is kind of interesting part of this page setup you can get it from the print menu but this part you have to go in the page layout menu in excel to kind of set that up and and then you could set it up and it's got enough flexibility to uh be able to fix it and and format your proper print menu in there and then obviously once you do it you could kind of do a print preview and it gives you an idea and and then do that but there are certain things like the menus are accessible but these kind of things are not accessible uh in in other areas so but so there's some nuances to excel relating to this in there now um the other thing remember our garbage in garbage out and when you're going through your grade school uh there's there's different terms that you had but i have this pemdas and you remember it by please excuse my dear aunt sally but it was the order of operations you have things that calculate in parenthesis next order was exponents that was multiplication division addition subtraction and this is how you did it manually and to a degree excel does this but don't rely on it because if it's simple enough a rule it will do it if it's not simple enough a rule go ahead and kind of put the appropriate parenthesis so the order of operations this rule is not violated otherwise you are going to get different results and all the more reason to validate anything you create in excel uh by doing that the the problem is some calculators have gotten smart about this so do it but don't rely on it because um like i said garbage in garbage i have so excel is still not there but neither is any software so if you are going to build uh very complex formulas in excel then that complex calculation the order of operations uh will be important and you should do that and you should validate it uh for sure and uh we looked at some shortcuts of the uh you know the old equals the sum but these are just some of the functions in average and and we use a lot of averaging in our you know uh metrology qa applications and last year um morehouse came up with a great cartoon about averages so i thought i would put that in and averages could be very dangerous um and i used to say this you can have your head in an oven fit in ice and on average you feel fine and morehouse came up with an application and showed you the danger of averages so here the aircraft weight is uh 30 000 plus or minus 300 pounds that's the specification for takeoff weight is the five repeated measurements they've done and taken in average and the average is perfect it's 30 000 of these five values but uh you could have a lot of issues with that because if we take these statistics on the average and look at it the the limits is plus or minus 300 but the variability of those five measurements is 10 times more so uh obviously when you use averages you should either look at and report the range or the associated standard deviation and so i just thought i would put that with the average function so and you know some looking calculation like that there's other things that happen to us and this is something that happens in measurement uncertainty i just had a email from one of my attendees in class and i said yeah we do this and they do a critical function and that test that they uh produce and the result uh that can send somebody to jail or that can send somebody to their freedom and claim them innocent and they they do two measurements and they they take an average now you're only taking two measurements and an average and i so i have to respond to that question and i said what is the right thing to do and the right thing to do there's other things we talk about in measurement uncertainty but a lot of times that's only the luxury you have luxury of making two measurements and two measurements better than one but it still has dilemma like this and here we have five measurements and the average is perfect and it is thirty thousand but the the variance associated with that is three thousand and that's not good because now you have a uh an error associated that's quite high and uh you can see the specifications if you could see these narrow lines is what it is the variability is this probability distribution curve so so those things can be critical in our decision so when you use average yeah it is there but for our application from metrology qa that could have large consequences in there but i just thought i would mention that and then there are other calculations so when you have a lot of variability in your measurements sometimes it's better to report a median that means fifty percent of values are above the median fifty percent are below the median and uh that's something you could have in there or the range and you could calculate the range as a difference of the max function and the minimum function in excel to use in there the other thing is we do square root root sum square and the square root function you could have a number or you could have a reference to a cell and yeah we'll calculate the square root for us the other way to calculate square root is you could have a cell reference raised to the power raised to the power of 0.5 that will give you square root 2 but there are other ways to do square root 2 if you wanted to do that so let's point those out the other number base you could use the exponent and then you could use the [Music] e euler's constant um and then the function uh for the exponent and uh you can apply uh to the base v in there as well and um excel has the natural log again or the the log function base 10 this natural log is base e in here and and those are some of the functions we may use uh if you were to calculate the uh the the probability distribution with the gaussian function that uses the exponent function and that works in there now henry mentioned in this chat the average if works well to ignore zeros but even the average will work and ignore zeros but if you wanted to be sure you could use the average if but uh average works and i've tried it both ways and i get the same result so i use the average by itself but he pointed out in the chat window so i just thought i would mention that as well in there and like i said the log function now the with the log function if you just use as a log then it will assume the base 10 but you could have any base in the log function so you could even have the base e in there and then it will work like the natural log that the ln function i had in the previous slide so uh so that's something that you could do now um there is this function deviation square and the other function is a sum square this is the sum of the squares this is the deviation square deviation of the squares so if we calculate our standard deviation if you were to manually calculate the sample standard deviation that calculation would be the sum of squares divided by degrees of freedom or base formula so you say oh sum of squares i can use this wrong you gotta use this in the numerator of the if you were to manually calculate the variance that is the function you use because if we break that formula it is the in the numerator it is the sum of individual values minus the average squared so the deviation square does that calculation the sum of squares it just takes the 5 7 9 it's 5 squared plus 7 squared plus 9 squared that gives you the sum of squares and there are times i've seen people use that but obviously you already have a variance function standard deviation function you're not going to use that but i've seen labs do some weird things for their specific calculation and they get the wrong thing and that's why the more reason for validating your spreadsheet and uh so um yeah so um the thing is uh i'm kind of reading henry's comment is yes if you have a zero in a cell then it will take it as a numerical value and it will include it in the average but if you have a blank cell then the average function will ignore that cell so that's the the key thing is uh so in that case you could use the average if function um that henry's pointed out in the chat window anywhere okay so ideally one of the things i do is like i would have a spreadsheet and i could put any number of values in there and in that case it will work okay and since i have a spreadsheet here i will show you what henry is talking about so i i here have data and it is doing a calculation so the way i have it and i i have nothing in the cells 6 6 through 20 then the average is gonna be that but if i put the zero in here like henry says then you could see the average did change but if i don't have anything then it will just count the sum divided by five in that one so so there are like caveats um so if i had average if then it's gonna ignore the zeros but the problem with that is in our measurement scenario we do have values that could be sometime very close to zero and and then you we have to like say do i want to include it or may not want to include it so i like using the average function because there are certain things that are very very small in excel okay so um i kind of use that logic and so so there's there's things you have to think about how you're gonna use when you create your excel templates for different different kind of things so um but the way i have it whether i use the average or average if it's going to give me the same results unless i have a value zero then excel will say yeah it has a zero is a value so it then counts it as an average and now it's going to divide by six so that's the the difference in that so that that's a good point that you made henry here and like i said excel has the the variance function and i say there are legacy functions and there are enhanced functions and i tend to use legacy functions because they work in either or excel claims that if you use these you get a little bit more precision i'm not sure about it because if they had a little bit more precision they should give me the same result or the same or they shouldn't give me any errors but it gives me the same errors that i got in the legacy function and excel has got some bugs in there and it reproduces that so i'm gonna stick with my legacy function for now every year i kind of decide should i just go to the enhanced functions and then i find out that somebody's needs the legacy functions so i kind of go ahead and keep it but i keep a close eye on what's happening out there and then i just i still try to use legacy unless there's a new function i want to use then and there are some other net functions excel comes out with all the time so we could use that and the same thing with the standard deviation functions with both sample population standard deviations so we have those functions in there and then it doesn't have the standard deviation of the mean function but i mention it because uh in measurement uncertainty calculations a lot of labs use this for the wrong reason so i just have it there just as a guide as a reference that if you do that you are dealing with as the term implies averages and uh not with individual measurements and so i just have that as a sort of a reference for you uh the other thing excel does like i said it is very good with statistical functions and [Music] with statistics there are population statistics distributions and small sample size distributions and in the bc days we would look at the back of the statistics books to get at this z distribution and t distribution tables excel can generate those tables and they can generate it the way you like it versus what's already pre-defined and so excel helps us do that and here's some functions to do the calculation and then create those tables or utilize that so and again these are legacy functions and there are the enhanced functions as well so one of the things like if you wanted the f1 key in excel is the help key and you could type in and it will give you not only the associated functions or it will get you to the area you want to and then you could look at it and i think the that help key as examples besides the function so it's a really nice way to like look at excel functions and a lot of times like i say i only know 10 of excel and if there's something i don't know if i can do i will go to the f1 key and type in and it will look at things and i may have to type in a couple different combinations but it it gets me there a lot of times um so uh and it is a useful thing now um the newer versions if you press the f1 key you do need an internet connection the previous versions had the help key with all the help files filled in and loaded into your excel but with the internet connection they go and update it more so you get more updated uh more enhanced help so uh but i just want to warn you if you don't have an internet connection it may not work in the newer versions of excel in there and uh so um there are things that uh henry and i were having a chat and doing the calculations and the new standards are about risk-based thinking and making statements of conformity and there are of course mathematical formulas we have to build in uh calculating uh the risk risk-based thinking and you could see i have used some of those uh statistical functions then how to calculate the uh the cpk the test uncertainty ratio in there and uh i just kind of give you uh whatever the uh the the the actual functions and the formulas that we use in there so uh it kind of gives you an idea of what's being used uh in in in excel to do some very very powerful calculations and like i said the statement of conformity is risks cpk is process capability calculations and those kind of things are built in there so uh and then uh i uh c7 has henry's saying i don't have the cell references in here um but uh i think i should probably put like the the cell references in here so you can see what the preference is is so i think i had it but on on the the new update i have i probably have the cell references in there so but the uh the other uh the t distribution table like i said in in the statistics books um you don't have all the probabilities and morehouse does force calibration to e74 e74 has a 98.4 or 2.4 standard deviations that comes out in 98.36 percent confidence interval so i can build those uh customized columns for the the appropriate standard deviation and the appropriate confidence interval so i could do that and in this next slide i show you uh those functions and again i'm using legacy functions and there are enhanced functions so if you press the f1 key and go to t i n we and the entire function is going to be t dot i n we i will do the same thing as the t i n v function and t dist will be t dot d i s d and so on like that so uh just to kind of give you an idea on these other statistical functions that excel can use and then uh the we look at the k coverage factor for reporting our expanded uncertainty and if we have the the common misconceptions k equals 2 but it k equals 2 at approximately 95 percent confidence interval at infinite degrees of freedom if you have different degrees of freedom then the k value could be different and using that t i and we calculation we could calculate the actual value of k in there and that will get us out of a mess of not over evaluating our uncertainty nor under evaluating the uncertainty because if we overevaluate the uncertainty under evaluated both have consequences and what i mean by that if i underevaluated the uncertainty i am passing something i should have failed if i over evaluate the uncertainty i'm failing something i should have passed and so so those are the things that uh help us do these calculations and before excel and with its uh powerful array of mathematical and statistical functions we were at a mercy of the tables in the back and sometimes those tables didn't give us the exact calculation if i go back to my t-distribution table somewhere down after now i created up to 30 but those tables went to 20 and the next row was 25 and 30. so if you have degrees of freedom 22 you were out of luck and you couldn't interpolate because these are not linear functions so that's not a good thing to do in that way okay so excel gives us a lot of flexibility to calculate things uh with a greater degree of confidence and with this risk-based thinking and reporting statements of conformity using the decision rules now this helps us formulate better decision rules in our calculations now excel has a random number generation function and by itself that number generation function gives us values from 0 and 1. that's not very useful now i can define and enhance that function by adding this where i can have an upper lower limit and it's going to generate numbers within this upper and lower limit now if this gives me a rectangular distribution function lower limit upper limit and i can now generate a lot of random numbers and you say what good is this um well you know in measurement uncertainty there's a monte carlo gum supplement excuse me and that is another way of calculating uh measurement uncertainty uh based on that and um that's another way to calculate measurement uncertainty using the random number generation function and i could do that and uh there are certain areas in measurement uncertainty this provides a better way of doing measurement uncertainty and there's there's nist has a uncertainty machine application that uses this uh technique but before the nist uncertainty machine came about i could do it using this way and just like this function generates the rectangular distribution as another one that does a gaussian distribution another one that does triangular and another one is u-shaped and we could do all of those using this technique using the random generation function in there so uh and you can see here i've created numbers between 10.01 001 to 9.999 in there so uh and then we looked at the deviation square and then the sum of squares uh before we started this workshop henry was talking about this uh different uh great characters and so here's the the unicorn for that and i've given you some of the character preferences that you could do the alt plus the uh the numbers in there now excel's got a symbol thing and you could do it but if you do this it will give you the appropriate greek letter in here so now um the random between number that tony pointed out here uh random between number is not a very good function because random between will just give you the integer values between the top and the bottom so i would refrain from using the random between number the random between numbers kind of nice like if you're kind of doing a a lottery raffle and you got like 32 participants and you want to pick a random number yeah that's good in there but the random between number for like what we want to do in measurement uncertainty that's not a good function okay uh now there's a since you mentioned the random between function uh there's a way to do that where you do not repeat these that integer value and their tricks to doing like that where you don't repeat it and i i have it somewhere saved how to do that but uh thanks for pointing that out tony yeah so okay so the other thing we do is convert numbers units pounds to kilogram and vice versa inches to matrix and so on the excel does have convert function and actually it is a good function and excel keeps adding units to that so you need to check from time to time before it was pretty limited it had like six units and i'm talking about years ago but it's gotten better and better every time so uh if i wanted to convert pounds to grams that would be here is uh one pound but you're gonna use the right lingo uh and then it will go and give you the value as i did here and so uh since we're talking about force and i said all right i'm gonna put in the weight and mass units so you you're gonna put the right nomenclature or the units as excel recognizes it and then your unit of distance and yeah i kind of like this the light years and parsecs so uh when i talk my star trek terms now i can think about those things in there okay and and then the pressure units and some force units in here so uh i have those in here and then charting tools um and in the morehouse website henry talks about calculating polynomial um you know and the calculating the right uh constants for polynomial uh curve fitting and so on so there's some already excellent reference in there and henry put that out earlier but just want to show you that excel can chart uh using the chart and then excel has a quick and dirty a trendline function that can give you like five different trend lines the linear one is one i've done polynomial it will do to the sixth order and then there's exponential logarithmic and a couple other ones that you have so that's like a quick and dirty way of doing things but uh from uh and then the the one that henry's given is the actual line esd function for the curve fit in there so and like i said i use a lot of the things i showed you in my measurement uncertainty template and i kind of went and showed you when i was doing that average uh to the to do those calculations in there so you could build some pretty elaborate templates for metrology qa creating control charts with the excel and they work very well uh in there and uh and they can do a lot of other things in there so i think this is probably my yeah last slide but i just wanted to give you like some of the things i use and like i said uh there's a lot many some of you shared some of the ones that you had in here so i thank you for your participation