Streamline Bill Making in Excel for Legal Professionals

Bill making in excel for Legal

today this is mr. Thompson I promised in class that I put together a quick video to show you how to put your data from your own little prick into Excel and create a graph with some error bars so let me see if I can do that reasonably quickly so just to recap our experiment was this we had our power supply which had the voltage and the current readings on it and we actually used a bit of our class used a bit of nichrome wire I think the other class mr. barkis class used a resistor and mystery resistor to try and work out the resistance and we did a number of trials so I'm going to steal Jordans data Thank You Jordan but before I do that let's just let's just put use Excel to build up table all right so our table needs to record basically voltage and current so let's start with some high-level voltage and that was an independent variable now for a voltage we want to record both the value the measured value and so let's just call that value so if it's their bill it correctly the value that we measured and the uncertainty so this will be the measurement uncertainty uncertainty so the measurement uncertainty which is based on the limit of reading with a resolution of our power supply so power supply now our let me put some values in so I think we measured from 0.5 volts and then we went up in increments of 0.2 volts 0.7 0.9 0.9 and enough data there that I can just use that I can grab that water fuel down to 1.9 I think that's the number of data points that we took yeah that's not quite right because our we haven't got the correct number of significant figures so what I should do is select all of those and you may remember that our voltage voltmeter on our power supply gave us a couple of decimal places so I'm just going to use the small place control there and add another decimal place so that's that was the number of decimal places that we had from your instrument now I uncertainty it was a digital instrument and so a uncertainty is one increment of the limit of reading so 0.01 volts without uncertainty so I'm going to copy that and uncertainly was the same for all of those readings so there we go now I'll put that voltage there I'm going to merge and center that and I might just make that cell a little bit wider so it can fit uncertainty in there there we go okay so there's our voltage now we also in media current so a dependent variable was our current current watch before even do that where I should go back here we are voltage was measured in if two to get back into that cell measured in volts measured in volts okay and over here we want current measured in amps all right now hey we did a bunch of trials here so what I'm going to do is I'll put a column for each trial so twelve one trial two you know I should be able to crack that in an auto-fill let me just see if I can do that trial three four five yes good okay so so there's my trial one two three four five once we have done all of those trials we're going to want an average and now it's going to be a mean so let's just call them in and call the average probably doesn't matter but let's do then now we also have a it's called a measurement measurement uncertainty a measurement uncertainty associated with their readings their current readings and look I'm going to record that you'll see that we probably won't even need the measurement uncertainty because our uncertainty of the mean uncertainty of the mean is going to be bigger than your measurement uncertainty and so a measurement uncertainty will be dwarfed Eclipse tip your life by the uncertainty remain yeah we could make those a lot bigger but I'm not going I'm just gonna peel pull that call me out of the air and that koala mental about it yeah then we're gonna select that whole row and I'm going to click this wreck ticks up here there we go so they took that a little bit better like that time measurement uncertainty this here for press f2 that was also a measurement uncertainty measurement uncertainty Fitz measurement uncertainty okay so we've got a measurement uncertainty in our voltage a measurement uncertainty in our current but we will also have an uncertainty of the mean which tells us pretty much what was the spread of data across our trials so the measurement uncertainty tells us how precise the measurement from our instrument 300 meter work and the uncertainty of the mean tells us what is the spread of data across all of our trials okay um let me make that look a little bit prettier so all of these all of these ones here they're all about current so what I'm do is I'm going to grab that current there and I'm gonna select all those cells and I'm going to merge in the center because they're all about current and I think I've already done it over here yeah maybe I did that without ever mentioning I just merged and centered the voltage there okay let's put some boxes around everything I like to color it seems a bit of blue for voltage and maybe a bit of green a current and make those cells let's make them white and control B let's fold them side of it easier Reed and what I'm going to do so these ones here are also voltage so I'm just going to make them sort of a light blue this is I'm getting bit you know sickly and these ones here I'm gonna make em a little bit green anyway crossing area okay so let's put the data in so I'm just going to come over and steal Jordans data from off the screen so you'll of course be typing this in my copy from there and come over to here okay so this is Jordan's data so these are all this is all current in amps and actually you know what I should do I've got 0.16 you know what my ammeter measured to three decimal places so I should display three decimal places in my table so I'm going to select all of those cells and I'm just going to come up here and it will increase decrease now they're all the same so now they're all about three decimal places so even these ones that end with a 0 that 0 is significant because it tells us that we actually measured to that uncertainty so or to that level of precision now I mean I'm just going to calculate the average here so I'm going to say equals average and then open my brackets and select all of these guys here want the average of all of them and close my brackets okay and then I'm going to take that formula there and I'm going to drag it down here so Auto fuels using that square little handle on the bottom of the square there ok there's all my means ok you know what happened when I pasted these in it got rid of my board it's something we just put them back in ok that won't happen to you because of course you'll be typing yours in right measurement uncertainty for current again this is probably not necessary because the uncertainty of the mean will be bigger so we will pretty much be able to ignore the measurement uncertainty but let's put it anyway for the sake of completeness 0.001 so the the ammeter that was built into our power supply measured to the nearest thousandth so that's our rule if we've got a digital scale we use the that was the the resolution of the instrument so that becomes our measurement uncertainty all right now the uncertainty the mean so you might recall that the uncertainty of the mean the way the qcaa likes us to do like this to measure the spread of our data is the uncertainty of the mean now you could argue that that the standard error standard error of the mean what you've done a measure of uncertainty and if you talk to your math teacher they'll probably tell you that's the case but I think the QC double-a wanted to keep the maths involved in physics reasonably simple so they've gone for a fairly simple approach so you might remember the uncertainty of the mean is simply the range of the data divided by two so I'm going to type equals so back I'm gonna say equals open brackets max so the max of 1 and max the maximum of all their trials - a minimum of all our trials against us like that same range the minimum of all our trials yep and then the max minus the min in brackets so together divided by 2 so that's how uncertainty of the mean absolute uncertainty of the mean so what that means is really that our value here is going to be our mean is zero point 1 1 2 plus or minus 0.006 amps okay now that formula there it works that this I'm just going to drag that whole formula down to these cells as well so we're using the same formula and there's our uncertainty of the mean now I'm not going to read these off yet I'm gonna carry that procedure through my calculations and through my graph at the end when we come up with with the resistance that's when we'll do some rounding but not you all right now we need to do a graph so what do we need to graph we don't need to grab all this stuff really our breath is really going to have the voltage that's this these cells here on one axis and we'll put them on their x-axis you could argue you might want to put them on the y-axis so that your gradient is resistance but let's go with tradition with tradition we're traditionally what we do or the convention is that we put the we put the independent variable on the X so that's what we'll do so there's our there's our voltage now I'm going to hold down control control and I'm going to come and take the mean of the current the mean of the current so that's really what we want our graph to be that on the exhale you know that when the x-axis buildings on the x-axis current on the y-axis so let's do that now so I'm going to go insert and I'm going to pick an XY scatter chart and I want one without a line now I could choose a line I think all that looks really nice but you know what that line is not a trend line that line is just a connect-the-dots line and it's not what we want so I'm going to put just dots dots only like then and then I'm going to add the trendline later so let me just put this in here and make it a little bit bigger okay [Music] [Music] why okay voltage versus current for the nichrome wire okay now let's label our axes so I'm going to click on the chart there notice I'm not clicking on anything on the Chive I click on erupts probably gonna select the grid line so I'm just gonna click on a blank space in the chart and then it's got a plus sign here so chart elements and I'm going to put some access titles in access titles so now I can now I'm just down here that's my voltage and voltage and voltage of V is we need to put the units in there as well up here on this one we want let's see current right counter current and measured in amps current measured in it's alright so voltage versus current now some error bars would be nice so let's add some error bars so again I'm going to if I click on plus here so that the graph selected I'll click on plus go to my error bars and I'll click on air bus now the error bars the Excel puts in by default and not the error bars we want now our standard error error bars let's have a look at that that's how I'm going to go to more options so from error bars here I'm gonna go more options okay and I've got horizontal and I got vertical arrow buttons now why horizontal error bars let's deal with those first my horns on what error bars will tell me the uncertainty in voltage now the only uncertainty we've got in voltage is measurement uncertainty because that was our independent variable we set the voltage so we didn't have to take averages but we do have an uncertainty and that's the uncertainty the measurement uncertainty so I've got my error bars there so what I'm going to do is I'm going to come down here and I'm gonna choose custom and specify value okay now it's gonna ask what I want for my positive error error value and what I want for my negative error value so my positive error value I'm just going to delete what's already in there and I'm going to choose my measurement uncertainty so I'm going to choose that whole column that measurement uncertainty that's what I want for my positive error value okay now I'm going to choose my negative error value and my name of your value is exactly the same thing so now for some reason I don't know about your excel my excel leaves then blank that's something really let me just know yeah but it's got it right okay so if I click away I can see those error bars there are error bars there but they are tiny and we expect that because we've got a very precise instrument so our error bars are very small let's look at our vertical error bars now a vertical error bars are the errors in or the uncertainty in current now our instrument now a meter measured current very precisely so say our error bars if we try to put those values and error bars we won't even see them but there's no need put to put those in because these errors here the uncertainty the mean in other words the spread of the readings that we got are actually quite wide so if we look at these ones here this is the spread that we got so I'm going to use those to create a terrible now medical error by so I'm going to click on the vertical every bar one of the vertical arrow bars I'm going to come over here again and I'm going to do customer and specify the value so the value nice to leave that delete that okay so the positive and the negative I want to use the uncertainty of the mean in other words I want this I want the error bars to show me the spread of data let's say you know click on negative error those ones as well again one doesn't display it very well yours might display better I don't know but I do it anyway and it seems to work so come down and go okay there yeah that seems to work so now I've got everybody you can see this was actually quite a precise experiment if every bars are quite small but they're there okay and now the next thing I want to do is put in my trendline so let's do that so I'm gonna click okay I'll click on the dots so I'm actually clicked on the data I'm going to go plus and I'm going to add trendline actually I might not have even had to click on the dots let's try that I think somewhere else as soon as that works still trendline yeah it does okay so click on trendline and look at automatically chosen a linear trendline which is what I want because I expect my expect a line I expect a straight line because I'm looking at a piece of wire which should obey Ohm's law so it should be linear and so if I wanted to I could I could choose but lynnie as many as the the sensible option in fact I could go more options and I can see I click over here if there's a whole bunch of trend lines I could put in I'm gonna stick with linear so excited it's one makes sense and if I wanted to I could come over here in fact let's let's display the equation on the chart and now you know what I could sit the intercept in fact I think I will set the intercept what this is doing is saying what's my y-intercept I'm gonna set my y-intercept to zero zero because I've got a pretty good argument that says if there was no voltage there was also no current you might argue that we shouldn't set the innocent because you might say that experimentally we didn't actually show that if there's no voltage there is no current so if you're not happy with my argument that there's no God there's no voltage there's no current maybe you could leave that unclicks now it will affect it doesn't affect the slope very much at all you see the slope as y equals zero point two two to eight if I don't sit in the intercept I've got a very small way to sit there and zero point two two one seven so it only changes the the gradient boy we only see the change in the third significant fear I'm gonna leave that as I think I've got a pretty good argument to say that zero zero is a balanced data point so the graph should go through zero zero so then y-intercept should be zero it was without in voltage we should have no current alright so there's our breath the only thing that remains now is we want to draw some lines that show the maximum and minimum gradients of this graph you have to do that I'm going to what I'm going to do is I'm going to print it out and I'm going to actually then manually draw in the lines of maximum or minimum gradients I don't know of a good way to do this automatically in Excel so if somebody who's watching does know please let me know put it in the comments or something but but I don't know how to do that so what I'm going to do is I'm going to add some more grid lines so that I can print this out and I can draw in a line of maximum gradient and a line of minimum gradient and then manually calculate the gradient okay so so if I click on this scale down the bottom here I should be able to actually if I go to plus here actually this will do it grid lines so click on grid lines I've got primary major primary major horizontal and primary manger vertical I'm going to put primary minor horizontal and primary Myer vertical and you can see they've put a lot more lines in so that means when I print my breath out I like she'd be able to when I draw some more lines I'll have enough group the gridlines will be good enough that I'll actually be able to read them and I'll be able to calculate the gradient of the minimum and maximum lines all right I think I'm done I'll see you in class