Excel online training course

excel lesson 4, Formulas

Using formulas

We use formulas to calculate values or apply mathmatic functions on cells.

There are two ways to descibe these formulas:
Syntax through a Range or the simple syntax.
Syntax means the way formulas are being written.
We can also use both syntaxes for more complicated formulas.

We use the "Simple syntax" to execute different calculations on different cells.
We use the "Range syntax" if we want to execute only one calculation on different cells.

Formulas can allways be changed at a later time.
To do this select the cell with the formula and make your changes in the formula bar.

Simple syntax formulas

To make a formula we first select the cell where we would like to have the result.
Then we type the = sign in the formula bar, formulas ALLWAYS start with a = sign.
And then we start typing our formula.

When we push the "Enter" key Excel will give us the result in our worksheet.
As you can see the content of cel C5 is still our formula.
If Excel does show the formula as text look if you didnt add a space infront of the = sign.
A formula MUST start with the = sign.
Once again, the actual content of the cell is the formula but what you see in the cell will be the result.

Range syntax formulas

When we want to insert "Range" syntax formulas, we will start just as in the "Simple" syntax formula with selecting the cell where we want to get the result and type a = sign in the formulabar.
Next we will type the function followed by opening brackets, followed by the range and as last we close the brackets and push the "Enter" key.
We dont use spaces in our formulas.
For example:
=SUM(B2:B4)
We start with the = sign, then the function and then the range between the brackets.

When we use both syntaxes in one formula then this could look as followed:
=SUM(B2:B3)*20%
In the first part we use a range and in the second part we use a calculation.

Formulas which refer to a cell will immediatly be adjusted when the content of this cell is being changed.
We dont need to update our formulas for this.
This is one of the best properties of Excel and one of the reasons why we dont enter to much numbers into the formulas.

When we insert cell adresses into a formula, we can type it or just click the cursor in the cell, or click and drag the Range of cells we want to use.
When the formula is entered we push the "Enter" key and the result will show up immediatly.

Arithmetics in Excel:

+ : Addition
- : Subtractions
/ : Division
* : Multiplication
% : Percentage
^ : Exponentiation

Excel uses a set order in calculations.
When you want to use multiple calculations into one formula it will get a little bit more difficult.
Excel will first calculate the percentage, then the exponentiation, then multiply or share those who are treated on the same level and are allways executed from left to right, and finally add and subtract which are also treated on the same level and are being executed from left to right.
Example:

Formula functions

In a "Range syntax formula" we can only give one single function to one or multiple Ranges of cells.
There are alot of functions we can use like Sinus and Cosinus and other complicated functions, but unless we are working on a level of a specialiced engineer or accountant we will allmost never use them.
The most obvious functions which we will use would be Sum, Average, Min, Max and Count.

Using Autosum

Many times we use Excel to calculate totals of columns or rows.
So often actually that they added a button in Excel for it, namely "Autosum".
This button will automaticly calculate the totals of rows and columns.
For this we will choose a empty cell at the end of a row or column.
And click the "Autosum" button.
Excel proposes us a Range from which he wants to calculate the sum.
If the proposal is not correct you need to click and drag over the cells which you want to see in your range.
And finally we click on the Autosum button once more or we push the "Enter" button.

A second way to do this is to select all cells in your range including one empty cell and click on the "Autosum" button.
Excel will show the totals of the cells in the empty cell.

In Microsoft Office 2003 and XP, the Autosum button is not only limited for calculating the sum.
But when you click on the black arrow on the rightside a dropdown menu will open with additional functions.

If the function you need is not in this list, you click on the "More functions" option.

More about this in the next part of this lesson.

Inserting functions in 2003 and XP

In Excel 2003 and XP we can insert functions in three ways:

1. In the menubar click on "Insert" and "Function".
2. Click on the "fx" button in the formula bar.
3. Or choose "More functions..." from the dropdown list you get to see when you click on the back arrow next to the Autosum button.

When you execute one of these actions the "Insert function" window will open:

In the "Insert Function" window we can search for a function by entering a few words in the "Search for a function" field and click on "Go".
A list will appear with functions related to your search from which we can make a choice.
Click on a function to see its syntax and desciption.

Or we can choose a function from the dropdown list by clicking on the arrow next to "Select a category:".
If you choose the "All" category you will see a alfabetical list of all functions in Excel.
Here also we can select a function and see its syntax and description.

Once a function is selected you click on the "Ok" button at the bottom.
This will open the "Function Arguments" window.

We can also click on the "Collapse/Expand Dialog Box" button excell_31.jpg (737 bytes) to close the window for us to select a Range in our worksheet.

Then we select the Range where we want to apply the function on.
We click on the "Collapse/Expand" button excell_32.jpg (697 bytes) once more and click the "Ok" button in the "Function Arguments" window to add the function into our worksheet.

Editing a Range in 2003 and XP

We can still change the Range of a formula even after we have entered it.
To do this, double click on the cell with the formula.
This will show our cell as a formula instead of the value and shows the Range in a blue border.
If we bring the mouse cursor to one of the corners of the border, we can increase or decrease the cells to change our range.
If we bring our cursor over a line of our border we can drag the entire Range to another location.

When you do either one of them the formula will automaticly adjust.

Autocorrect formula

When you enter a formula incorrectly, Excel will try and assist you in writing the right syntax.
When you insert extra spaces or calculations, Excel will show a window where he proposes the right syntax.

Click on "Yes" to accept the proposal or click on "No" to make your own correction.

Autocalculate

Autocalculate is a temporary aid which will show the results of simple calculations into the statusbar without you needing to type a formula.

Select the Range in your worksheet.
Now click the right mousebutton on the statusbar, this will open a dropdown menu where you can make choice from other functions.
Whatever choice you make here, the result will be shown in the statusbar as long as the Range is selected.