Why Do You Need Formulas and Functions?

What is Excel and Why Do We Need It?

If you are an office administrator, you probably use Excel. Ditto if you are a salesman. If you are a financial analyst, of course, you need Excel. For these and many other professions, working with spreadsheets is an essential tool in the daily routine.

People who are math wizards will probably immediately take to Excel and use it like a natural extension of their computational processes. Most of the rest of us … probably need some help in that department.

If you did not pay attention in high school algebra class, don’t fret! All you need to do is learn some basic knowledge of formulas and functions from this How-To Geek School series and apply them to your work.

If you don’t know whether or not you need Microsoft Excel, then you probably have not been shown what it can do. Once you do know, then you can find ways to use it in your work to make complicated and tedious tasks easier.

Microsoft Excel is more than just a program to enter names and numbers. For example, if you simply want to list the names of students in your class then you can just make a Word document. Word is fine for writing essays and making lists but to do calculations, you need Excel’s formulas and functions.

A Brief Tour of the Excel Interface

Before we dive in, let’s just briefly review the Excel interface. You may already know all this stuff, but it never hurts to review.

When you open a blank Excel spreadsheet, it is known as a “workbook” or “book” and each spreadsheet in a book is a known as a “worksheet” or “sheet.”

The top of the app is dominated by the “ribbon” (gray), which subdivided into “tabs” (red), which are then further sub-divided into “sections” (blue).

Below the ribbon is the “name box,” which lets you rename “cells” and then to the right of that is an area that allows you to create your all-important “formulas.” If you hover over each icon, you can see the purpose of each button, but what we’re most concerned with is the wide area where our formulas and functions will be displayed.

Finally, the worksheet is arranged in “columns” (denoted by letters) and “rows” (indicated by numbers). Each location is a “cell” and a group of cells is known as a “range.” If you have multiple sheets in a book, you can quickly flip through them using the tabs seen below the sheet. Below that, in the gray status bar, you can adjust your page views and zoom level.

That’s it, pretty simple, and just a short overview of some of the terminology you’ll encounter in this HTG School series.

What is a Function?

A function is a calculation or operation that returns a result. The inputs in a function are called “arguments.”

All functions begin with an equals sign [=]. That way Excel knows not to treat the arguments as text. For example, =AVERAGE(2,4) is a function but AVERAGE(2,4) is just a string of text. Without an equals sign, Excel will not calculate a result. The arguments in this function are 2 and 4.

Note, Excel uses upper-case letters to list functions, but you can use lower or upper-case letters when you write them.

In Excel, the “Function Library” can be found on the “Formulas” tab.

There are 13 categories of functions, some of which are:

• Mathematical: AVERAGE() – calculates the average of a series of numbers.
• Date and time: DATEVALUE() – converts a string of text like “30 November 2013” to a number so that you can use this number in other date and time functions. You cannot do math with dates unless you convert them to numbers first. We explain this in detail in Lesson 4.
• Text: LEN() – returns the length of a string. For example =LEN(“Excel”) is 5.
• Logical: IF() – the IF() function is written like =IF(<test>, then A, else B). So, if “test” is true, then the result is A; if “test” is not true, then B.
• Lookup and Reference: These are needed to lookup values elsewhere in the spreadsheet. For example, VLOOKUP looks in a table of values to find one cell.

How could you use this last one? Well, to get the day of the week in text from a date function. You can use VLOOKUP to scan a table to turn this number into something easier to understand, like “Wednesday.”

There are also special functions for financial, engineering, and statistics which are listed separately on the “More Functions” menu.

What is a Formula?

A formula is combination of “operators”, “operands”, and “functions.”

For example, the function =SUM adds a list of numbers (it is so commonly used, that is listed on the first menu in Excel, abbreviated by the Greek letter Sigma (Σ), which is the notation that mathematicians use to sum a series).

You use a formula like doing a calculation by hand. For example, you could put your family budget into a formula like this:

Remaining cash = (4 * weekly salary) – mortgage – food – utilities

The operators are multiply [*] and subtract [-]. The operands are the values “weekly salary”, “mortgage”, “food”, and “utilities.” The result is “remaining cash.”

Names and Addresses

The values for “food” and the other operands are names that you define in Excel. Without a “name,” you would have to use the “address.”

The address of a cell is written using row-column notation. The rows are given numbers and the columns, letters. The first cell in the spreadsheet is A1. When you have reached the end of the alphabet, the rows are numbered AA, AB, BA, BB, and so forth.

Formulas can be more complicated than the family-budget example. In high school you learned that that the area of a circle is the radius times pi squared or πr².

In Excel, you can write this using the formula =PI() * radius ^ 2.

Here, PI() is the function that returns the number 3.14 and “radius” is a “name” we have given to a cell that contains the radius; the “operators” are the exponent (^) and multiplier (*).

Order and Precedence

Parentheses are used to indicate the order and precedence in calculations.

The area of a circle can also we written πrr but not (πr)², so you need to understand order and precedence to get right answer. Exponents are evaluated before multiplication, so parentheses are not needed in this case. The function squares the radius first then multiplies that by Pi.

If you wanted to eliminate any possible doubt you could be explicit and write =Pi * (radius ^2).

---

Courtesy: howtogeek.com