Using Excel Formulas

Quick reference

• 00:04 Okay, in this video I want to talk about using formulas in an Excel Spreadsheet.
• 00:07 So if you've used Excel Spreadsheets in the past you have likely used formulas.
• 00:12 And by formulas, I mean, adding up the sum of a certain number of cell,
• 00:17 finding the average of a certain number of cells, things like that.
• 00:21 So I've created a new file called widgets.py.
• 00:24 And I've got our basic starter code here, and we're loading a spreadsheet called
• 00:28 widgets and setting the active worksheet and then we're saving it as widgets2.
• 00:32 So this widget spreadsheet, we can go ahead and open it here,
• 00:37 it's just a very basic spreadsheet.
• 00:40 And I've got pretending we're a widget company, we're selling blue widgets and
• 00:43 red widgets and green and purple and yellow widgets.
• 00:46 And this is the number sold.
• 00:47 So if we wanted to, for instance, find out how many total widgets we sold,
• 00:52 we might use a formula that looks something like this.
• 00:55 Where we create the sum of all of these things and we see it's 2300, right?
• 01:01 We might want to find the average, we can do a formula for
• 01:05 the average, whatever you want to do averages 460.
• 01:09 You probably used to doing formulas in an Excel spreadsheets.
• 01:11 So how do we do this with Python, well,
• 01:13 that's what we're going to look at in this video.
• 01:14 So, let's go ahead and close this and head back over to our code, and
• 01:19 to use formulas with our Python code, we just write the formula right here.
• 01:23 And then just pass it into a cell like we would pass anything else.
• 01:28 So, for instance, if we wanted to put something in B8,
• 01:33 well, we already know how to do that.
• 01:36 We would set ws and then B8 to = and
• 01:40 then we could put, something.
• 01:44 Now, if we save this and ran it, and opened widgets2,
• 01:48 we would see "Something" and "B8".
• 01:51 Let's, go ahead and do that real quick just to make sure, so
• 01:54 let's go python widgets.py.
• 01:59 And now we can come over here and open widgets2, and we see sure enough,
• 02:02 there's something listed right there.
• 02:04 So that we can close this and head back over here.
• 02:08 So, instead of passing something to use formulas,
• 02:11 you actually just pass the formula inside these quotation marks.
• 02:15 So if we wanted the sum, Of,
• 02:20 and let's take a look at our widgets again.
• 02:23 So say if we want to B2 to B6, we would just pass
• 02:29 B2 to B6, just like you would normally.
• 02:33 So if we save this and before we run this, I want to just very quickly show you,
• 02:38 if we were doing this in the Excel spreadsheet,
• 02:41 it would be sum and then we would just highlight all of these.
• 02:44 Now, if we look right here, this is the exact formula, right?
• 02:48 Sum in fact, I can copy this, if we head back over here and
• 02:52 paste it in, you'll notice it's the same exact thing, right?
• 02:57 So we're just, whatever formula you want to use in your Excel spreadsheet,
• 03:00 you can just pass it in with these variables.
• 03:02 So, okay, let's go ahead and save this and run it, And
• 03:06 now if we open up our widgets spreadsheet,
• 03:09 we see the formula has been passed and the output is 2300, and that works.
• 03:15 If we wanted to get really fancy we could put something in A8,
• 03:21 right, though we could just copy this, and
• 03:26 let's say, A8, it's a Total, right?
• 03:30 We save this and run it, and then open up our widgets2 spreadsheet,
• 03:35 we see it says Total, and we have the Total here.
• 03:38 So, really simple way to pass any sort of formula that you want into your
• 03:42 spreadsheet using Python.
• 03:43 So in the next video, we're going to look at grabbing cell ranges.

Lesson notes are only available for subscribers.