Level 2 - Lesson 12 - Worksheet Functions

Category: [General,Excel] Demo Available

Worksheet Functions and Formula in Excel

In this lesson we will look at the thing that Excel does best and that is calculations. Excel has a multitude of built in functions that can perform simple additions through to some very obscure engineering functions. Sadly most Excel users never get much past the simple addition, division, subtraction and multiplication. I guess most people do not enjoy math and so have no real desire to revisit it once they have left school.

While I admit I was one of those students that dreaded math, I now tend to look at it from different angle than most. That is, if there is a program that can basically do it for me then I'm all ears! I am certainly no mathematician when it comes to formulas, but I very rarely get stuck in writing an Excel formula. The reason is simply because I know how to use Excel in a way that enables me to perform calculations that I couldn't do if my life depended on it if I only had pen and paper. What I will endeavor to show you in this lesson is not how to perform calculations, but rather how to get Excel to do it for you.

For ALL formulas in Excel there are two rules that MUST be followed.

All formulas must begin with an = (equal sign)
For every open parenthesis there must be a closing parenthesis. However this is not to say that all Excel formulas must have parenthesis.

In Excel the term formula refers to a formula or function(s) in its entirety, whereas the term function refers to only one particular function. For example: =IF(A1=20,"yes","no") is the formula, while the IF is the function used in the formula.

So we are on the same wave length we have included the text from Excels help on the terminology commonly used.

Operators that Excel Recognises

The text below is from the Excel help file:

Calculation operators in formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Arithmetic operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition	3+3
– (minus sign)	SubtractionNegation	3–1–1
* (asterisk)	Multiplication	3*3
/ (forward slash)	Division	3/3
% (percent sign)	Percent	20%
^ (caret)	Exponentiation	3^2 (the same as 3*3)

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	A1=B1
(greater than sign)	Greater than	A1>B1
< (less than sign)	Less than	A1<B1
>= (greater than or equal to sign)	Greater than or equal to	A1>=B1
<= (less than or equal to sign)	Less than or equal to	A1<=B1
<> (not equal to sign)	Not equal to	A1<>B1

Text concatenation operator

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator	Meaning	Example
& (ampersand)	Connects, or concatenates, two values to produce one continuous text value	"North" & "wind" produce "Northwind"

Reference operators

Combine ranges of cells for calculations with the following operators.

Reference operator	Meaning	Example
: (colon)	Range operator, which produces one reference to all the cells between two references, including the two references	B5:B15
, (comma)	Union operator, which combines multiple references into one reference	SUM(B5:B15,D5:D15)

End of MS Excel Help file

When Excel performs a calculation it does so in the following order:

Calculations in brackets worked out first
Multiplication and Division
Addition and Subtraction

If a formula contained both a multiplication and a division operator Excel would calculate them from left to right. The same would apply for subtraction and addition. We can change the order in which Excel does its calculations by closing the relative function in parenthesis. Let's say we had the formula =10-10*10 the result would be -90 (negative 90). If we then used =(10-10)*10 the result would be 0 (zero). In other words we have forced Excel to change its natural order of calculation. Excel is quite happy to do this.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.