Excel: Using Formulas and Functions for Calculations

I know that out of all the Microsoft Office tools (Word, PowerPoint, and Excel), Microsoft Excel is probably the least used among college students, simply because we normally do not have a large need to create spreadsheets, but have a daily need of creating Word documents or PowerPoints.  However, there are aspects of Microsoft Excel that we can use now that we may often overlook.  For example, college students can use Excel to create a budget or keep track of their grades.  As a future teacher, I realized that there are a plethora of uses for Excel for educators.  From keeping track of grades to who has completed an assignment to which grade sold the most items for a school fundraiser, Excel provides an easy and clear way for teachers to stay organized.

Lynda.com is a website that offers tutorials on how to operate and work with different tools and technologies, such as Microsoft Excel.  I explored Excel for Mac 2011 Essential Training, specifically focusing on Chapter 3 Managing Worksheets, Cells, and Cell Data, video 3, and Chapter 4 Summarizing Data Using Formulas and Functions, videos 1, 2, and 3.  These videos taught me so much about the tools of Microsoft Excel that I did not know existed before.  If you are a Windows user, don’t worry, because videos for the same topics can be explored through Excel 2010 Essential Training.  Related information in this tutorial can be found under Chapter 2 Worksheet Basics, video 3, and Chapter 3 Excel Formula Basics.  

The video in Chapter 3 entitled Entering Data Using AutoFill and Other Techniques was super interesting in the sense that it taught me lots of tips and tricks on how to make entering data into Excel faster and more efficient.  For example, if you are creating a spreadsheet and need to enter in the months of the year, instead of typing out every single month, you can use AutoFill.  If you type January into one cell, then click the bottom right corner of that cell and drag down the number of cells that you need months, AutoFill will automatically fill in the cells with the appropriate months.  This process is shown in the pictures below.

Example of AutoFill for months of the year in Excel spreadsheet

Example of AutoFill

Example of all 12 months filled in using AutoFill in Excel

All months inserted using AutoFIll

Spreadsheet of different AutoFilled topics

Examples of categories that can be AutoFilled

AutoFill can help you insert more than just months though.  In this screenshot, you can see that AutoFill can automatically fill in the number of cells you need with the full names of the months, abbreviated versions of the months, quarters, periods, weeks, and days of the week.  All you have to do is type the word in the first row, then as you pull that cell down, Excel fills in the rest of the rows for you.  Using AutoFill to create these categories saves time and makes using spreadsheets a lot less tedious.

In the first video in Chapter 4, entitled Introducing Excel Formulas and Functions, you learn all about the difference between a formula and a function.  A formula is one that you enter into Excel by hand.  A function however, is a tool that Excel offers that will automatically calculate whatever it is that you want calculated when you select a particular function.

Example of how to average a column in a spreadsheet

Unaveraged class average spreadsheet

In the example shown here, a teacher has a spreadsheet of test grades for her class.  If she wanted to find the class average for each test, she could use the AVERAGE function, which would automatically determine the average of whatever numbers she wanted.  In the second picture, you can see that she has found the class average for each test, which is the average for each column, and she has found each student’s individual test average, which is the average for each row.

Example of completed spreadsheet with averages

Averaged spreadsheet

Using the AVERAGE function is much faster than typing in a formula to find the average.  If you used a formula to find an average, you would have to add all the numbers in a column or row, then divide by the total number in each particular column or row to get the average.  That is a lot of tedious typing to create a formula when you could just use the AVERAGE function that does it for you.  This is a great shortcut to use to save time and energy.

The second video in Chapter 4, Adding a Formula to a Cell, discusses a lot of the same things that video one covers.  However, it highlights other functions besides the AVERAGE function.  Excel has created a wide range of functions that can be found through the formula builder or reference tool at the top of the formula ribbon in Excel, shown below.  These pre-made functions include SUM, MIN, MAX, or COUNT, just to name a few.

Formula ribbon bar with function finding tools

Ribbon Bar for Functions

If we use the same spreadsheet example from above about the class averages, we can find the minimum and maximum grade for each test by using other functions in the same spreadsheet.  To find these values, we must first start by clicking the cell and entering in the “equal” sign.  It is important to note that any time you are entering in a function in a cell, you must signify that it is a function by first typing an equal sign =, followed by the name of the function.  So after we type the equal sign, we can type in the word MAX, and the MAX function will appear.  Once you enter the cells that you want to find the max of, hit enter and the maximum grade for that test will appear, as seen below.

Gradebook spreadsheet with maximum and minimum test grades

MAX and MIN test scores

Spreadsheet showing which function was used for different values

Which function was used?

The screenshot to the right shows the same class average spreadsheet as above, but instead of the averaged number or maximum or minimum, the function that was used to find that value is shown.  This was done by typing “control ~”.

When using the same function across multiple rows or columns, like we did above when finding the class average grade, maximum, and minimum for each test, we can use what we learned in Chapter 3 about AutoFill to make finding the values even easier.  Once you find the average, max, and min for the first test, you can use AutoFill by dragging across to the last column, and AutoFill will find all your values without having to type in the function each time.  The same is true for finding each individual student’s test averages.  All you have to do is type in the formula for the first student, then AutoFill down for all the rest.  AutoFill addresses each cell individually, whether you are doing class average or student average, so that the formula is correct for each cell.  This is very handy and makes finding values with functions even more efficient.

If the numbers in your spreadsheet are not all formatted the same, like in the class average spreadsheet where some averages are whole numbers, some have one decimal, and some have two decimals, you can learn how to format numbers to look neater in Maria H.’s blog post Formatting a Microsoft Excel Spreadsheet.

Once you have all your data, you might want to make a chart or graph to help analyze the results.  For example, in the class average example, if you wanted to see which test overall  your students did better on, you could make a chart or graph to determine the results.  You can learn all about how to do this in Excel by reading Winna P.’s post Excel: Creating Basic Charts Quickly.

The third video in Chapter 4 is called Introducing Arithmetic Operators.  This video focuses on how Excel solves operations without a function.  If you remember way back to elementary school math when you learned about our good friend Aunt Sally- or PEMDAS (Please Excuse My Dear Aunt Sally)- when solving order of operations problems, then you know how Excel solves non-function problems.  If you don’t remember, no worries, video 3 will teach you!  Excel, just like you when solving an order of operations problem, does not solve the problem from left to right, but instead uses a specific order in solving the problem.

Example of what order of operations to enter to convert Fahrenheit to Celsius

Order of operations for converting temperature

Using order of operations in an Excel spreadsheet can be seen in this example, which involves converting degrees Fahrenheit to degrees Celsius.  In order to convert degrees Fahrenheit to degrees Celsius, you must enter in this formula: (F-32)*5/9.  Excel solves this formula by first subtracting 32 from whatever degree Fahrenheit you are trying to convert because this is the action in the parentheses, which always comes first.  Then, since multiplication and division are in the same step of order of operations, Excel solves the rest from left to right.  The value in the parenthesis is multiplied by 5, then that value is divided by 9.  Knowing the order of operations is useful because it allows you to know that how you enter in your formula matters, because a simple change of placement of parenthesis will result in an entirely different answer.  For example, if the parentheses were not there in this conversion, the formula would look like this: F-32*5/9.  The order of operations would do 32 times 5, divided by 9, and then that value would be subtracted from the degree Fahrenheit.  So instead of subtracting 32 first, then multiplying and dividing, the subtraction would be the last step which would produce an entirely different, and wrong, answer.

When entering in this formula, you again can use AutoFill.  Instead of typing in each degree Fahrenheit into the formula for each cell, you can type in the cell address.  So for the first formula to convert 32 to degrees Celsius, you would enter in (A4-32)*5/9.  Once you do this for the first cell, you can drag the cell down, just like before, and AutoFill will change the formula automatically for each row, determining the conversion to degrees Celsius.

I invite you to comment below your ideas on how having this knowledge of tips and tricks for formulas and functions in Microsoft Excel will increase your overall use of Microsoft Excel and how it will decease your possible frustration with entering and finding data in a spreadsheet.  How do you think using Excel is beneficial to teachers? Are there any ways you think students can use Excel (both you as a college student and your future students)? How helpful is this information? How much did you know about Excel formulas and functions before?

Advertisements
This entry was posted in lynda.com, MS Excel, Spreadsheets. Bookmark the permalink.

13 Responses to Excel: Using Formulas and Functions for Calculations

  1. Sara R. says:

    Anna, this is probably one of the most well-written posts I have read thus far. Not only does your introduction depict an honest viewpoint of Excel to college students but also the way you wordsmithed your question at the end was quite, for lack of a better word, inviting. I had to comment on that, given that I am a very English-minded person. Now, on to the actual content of your post: I found it to be both detailed and quite easy to follow. I was aware of the mathematical functions in Excel, but I had not used or spoken about them since around seventh grade, so I had forgotten about them. But now, due to your explanations, I will use them whenever I need to compile numbers in such a manner. I can even see myself using this in the future to find the average of my class’ test scores when I have students of my own.

  2. Tabitha C. says:

    I love how you recognized in your first paragraph that Excel is often used less than the other Microsoft Office applications. Keeping track of grades with it is definitely a good point. My Sign Language teacher has us do that this semester, and it’s much better than my old fashioned way of writing it all down in the back of my planner. I can even put in the due dates of assignments to make sure I don’t forget to plug a grade in. Like Sara, I think this post was very well written. Great job Anna!

  3. Steph G. says:

    Anna, your did an amazing job at explaining the most difficult parts of Excel. I totally agree that it is the least used application in Microsoft Office, probably because it is so confusing. However, I did not realize there were so many shortcuts that make it so easy, such as Autofill. That seems like a really convenient short cut that many would use. Also, you do a great job at explaining how to create equations in Excel, something that I find to be very tricky. You did a great job!

  4. Victoria B. says:

    Anna, I found your blog post to be incredibly informative. Since I rarely use Excel (as you pointed out, few college students do!), I had no idea that the program could be simplified through the tips you provided. I always find the program terribly difficult to work with so your post was definitely helpful! I was especially interested in the autofill option. I didn’t know that Excel could do that and I will definitely take advantage of that feature in the future! I also loved your screenshots-they supplemented your information very well!

  5. Maria H. says:

    Wow, who knew the things you could do with Microsoft Excel! I had never experienced or even heard of the AutoFill tool before this post, and how helpful that is! Also, how does a computer program know to use Please Excuse My Dear Aunt Sally? That is too wild! All of the different functions that Excel can perform is crazy, and your post explained them perfectly. I think everyone is quite intimidated by Excel, but after this post, I feel much more comfortable with using formulas and functions in my classroom!

  6. Winna P. says:

    Hi Anna! Your blog post was so great. Although my post was also related to spreadsheets, I am not the greatest with them; especially when it comes to the mathematical side of it! You tackled the hardest part about spreadsheets and did a wonderful job explaining it in a way that is easy to understand and apply. This post has inspired me to hopefully one day utilize Excel for grading purposes. It would be a good way to track grading as a class or an individual student. Thanks so much for sharing information on such a difficult topic, once again, you did an excellent job with it.

  7. Sydney R. says:

    Really great post Anna! I’ve always found Microsoft Excel so intimidating, but the way you explained everything in such a clear and thorough manner makes me feel way more at ease about using it in the future. The AutoFill tool is so neat and practical and I had no idea it even existed. I can definitely see myself using Excel with my students in the future, and I will have to keep in mind all of the new features you talked about, such as the average function.

  8. Chelsea S. says:

    I feel like knowing the mathematical side of excel is the most important part of it! People can create as many spreadsheets as they want but eventually they will want to compare one part of their sheet to another. Knowing how to create the average, find the maximum, and finding the minimum is very informative! One of the ways my future students can use these techniques is by having them keep track of the weather daily and finding the average for the month with excel spreadsheets. When I shadowed a Kindergarten class in January they were just learning the basics of the weather saying if it was hot or cold outside. By using excel sheets, students can delve deeper in what they learned in the previous grades and take their knowledge to the next level! Thanks for an awesome post, it really made me think of different ways I can incorporate excel when I become a teacher!

  9. Mary F. says:

    I love that you started out your post relating it to the audience. That really got my attention and made me feel like you understand (which you do). Your post was so informative and really helpful considering how little I’ve used excel before. Autofill literally saved my life. It is such a simple tool that will save so much time so that you don’t have to go through and do everything one by one. You made a really good point about being careful when typing in equations, one wrong symbol or number and it could mess up your entire spreadsheet. This post has made excel so much easier for me to use and I’m so appreciative for all the helpful tools and hints you’ve given me.

  10. Alexia M. says:

    Anna, learning about all the different formulas and functions for calculations in excel must have taken some serious thought processing, so I commend you on taking on the challenge of understanding all of this! You explained it so well, and it made me a lot less nervous about using excel and using functions and formulas. I never knew that autofill was an option to use, nor did I really know how to use functions. Now that I know that these are able to use, I feel much more comfortable. This will definitely help me when I am keeping track of grades or other things in the classroom.

  11. Katie H. says:

    Similarly to what other people have said before, you have written one amazing post Anna! There is no way that I would ever want to tackle trying to explain the processes within Microsoft Excel, yet you managed to do an amazing job. I know that after reading this and watching the Lynda.com videos I will be able to actually use this program for more than just making a list. I really enjoyed learning about using the feature that calculates averages, which will be extremely helpful with determining grades on tests or assignments. I never realized how helpful Microsoft Excel could be in the classroom, but now I know different ways that I can use them! Thanks so much for a great post!

  12. Angie E. says:

    Great Job Anna! This post was super useful in explaining all the calculations Excel can do. The last time I tried to calculate things using Excel was in my seventh grade Keyboarding class. Needless to say I’m a bit rusty and need all the help I can get. I had to wrestle with a few spreadsheets in class to get the hang of writing the formulas and using functions, but I couldn’t have done it without your post!

  13. Laura H. says:

    This function of the microsoft programs has been a real struggle for me in the past. I really appreciate the awesome explanations you give, especially of Autofill, which is such a useful and time saving tool!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s