Excel Formulas: It’s Not Math, It’s Magic

For this post, I’m using sample the file Excel4EngMajors_TheSoundAndTheFury.xlsx.  If you want to follow along, you can download the file here or on the Resources page.

Image credit: “Witch Magic Wand Magic Bounce” by Adina Voicu is liscensed under CC BY 2.0. Changed from original: Cropped; text added.

Okay, we’ve avoided it long enough – it’s time to dig in and actually do some Excel.  

I know what you’re feeling – your heart is starting to race.  But hang in there.  You’ve made it this far – you can do this.  

Plus, let me let you in on a little secret: you don’t need to do any math to use Excel.

What did you just say.

You don’t need to do any math to use Excel.

Wait.  No.  What?

YOU DON’T NEED TO DO ANY MATH TO USE EXCEL.

Shout it from the rooftops!  Write a pop hit about it!  Text it to your mom!  We have found it!  Salvation!

What magic is this, you ask?

No magic my friends.  Just formulas.

What is a formula?

A formula is a command entered into a cell that does a task.  Frequently, this task is a math equation, like adding a group of cells together to figure out their total.  However, it doesn’t have to be strictly math – Excel is also a powerful tool to manipulate text, but we’ll get into that later.  

Formulas start with an equals sign =.  Formulas that are simple math can just reference which cells are being used, for example =A1+B1.

But these formulas are just the tip of the iceberg.  The real power in Excel comes with formulas that have a function.  A function is a word that determines what the formula will do, like SUM or COUNT.  After the function, there is an open parenthesis (.  This tells the formula that you are starting to give specific instructions, like telling the formula which cells to look at.  At the end of the formula is a close parenthesis that signals that the formula is complete.

Let’s do an example, shall we?

The Sound and the Fury is a book.

It’s a book with a complex plot and a structure to match.  The book is broken down into four sections focused on different characters, happening at different points in time.  And Excel can help us make sense of all this.

No, but really.

To start, I have a spreadsheet with some general information–the narrating character, the date of their chapter, and their start page and end page.  Let’s say that we want to see what the average chapter length is, so that we can compare how long each section is to see if longer or shorter sections may signal more emphasis in the book.

To start, let’s figure out how many pages are in each chapter.  To figure that out, we want see how many pages it takes to get from the first page of the chapter to the last page of the chapter.  In other words, we want to do this:

(Pages Per Chapter) = (End Page) – (Start Page)

Now let’s build our formula.  Formulas always start with the equals sign.  Then click on the cell with the End Page number.  Then type the minus sign.  Then click on the cell with the Start Page number.  In other words, this is your formula:

=D2-C2

Click enter, and voila!  You have done your first Excel formula!  Take a moment to celebrate, this is a big moment!!

Once you have the first formula done, the rest is easy.  If you want to do the same thing in multiple places (in other words, figure out the total pages per chapter for the other chapters, which we do), we can take our first formula and use it for our other sections

To start, copy the cell where the formula is.  What Excel is doing is copying the FORMULA, not the NUMBER.  Now, select the new cell and paste.  Again, this pastes in the FORMULA, referencing the relevant cells, so it is now calculating the pages per chapter for the new line.

You can paste your formula as many times as you want, and have it do the work for you!  Just like magic!

To finish up, let’s figure out the average across all the chapters.  To do this, we are going to use the Excel function “AVERAGE,” which tells Excel to take an average of any cells we choose.  

We are going start as always with an equals sign.  Then type in the word AVERAGE to tell Excel which function we want to use.  Then, type an open parenthesis, and click and drag your cursor over the cells you want to select.  This tells Excel which cells you want it to average.  Then type close parenthesis.  

Hold your breath for this part.

Click enter.

WOAH.

AMAZING.

Now we can see that the average number of pages is about 78 per chapter.  Comparing that to the individual chapters, Benjy’s and Jason’s sections are closest to the average.  However, Quentin’s and Dilsey’s chapters have much bigger variances, which suggests that these chapters might have the most important literary emphasis, when compared to the other ones.

You’ve done your first Excel spreadsheet!  I am SO PROUD OF YOU!  How did it go?  How did you feel?  Let me know in the comments below!