Excel 2 Marco Pires IT Training and Empowerment
Formulas Writing Excel formulas is a little different than the way it is done in math class. Excel formulas starts with the equal sign ( = ) rather than ending with it. The equal sign informs Excel that what follows is part of a formula, and not just a name or a number. Excel formulas look like this:=3 + 2rather than: 3 + 2 =
Formulas To find a cell reference, simply look at the column headings to find which column the cell is in, and across to find which row it is in. The cell reference is a combination of the column letter and row number -- such as A1, B3, or Z345. When writing cell references the column letter always comes first. So, instead of writing this formula in cell C1: = 3 + 2 write this instead: = A1+A2 If you have typed the = then clicking the cell will reference it.
Formulas Open the spread sheet named "formulas1" When you use cell references in Excel formulas, the formulas will automatically update whenever the relevant data in the spread sheet changes. For example, if you realize that the data in cell A1 should have been an 8 instead of a 3, you only need to change the contents of cell A1. Excel updates the answer in cell C1. The formula, itself, doesn't need to change because it was written using cell references. Changing the data Click on the cell A1 Type an 8 Press the ENTER key on the keyboard The answer in cell C1 where the formula is, immediately changes from 5 to 10, but the formula itself is unchanged.
Mathematical Operators The mathematical operators used in Excel formulas are similar to the ones used in math class. Subtraction - minus sign ( - ) Addition - plus sign ( + ) Division - forward slash ( / ) Multiplication - asterisk (* ) Exponentiation - caret (^ ) Order of Operations If more than one operator is used in a formula, there is a specific order that Excel will follow to perform these mathematical operations. This order of operations can be changed by adding brackets to the equation. An easy way to remember the order of operations is to use the acronym: BEDMAS The Order of Operations is: BracketsExponentsDivisionMultiplicationAdditionSubtraction
Functions What is a function? A function is a pre-set formula in Excel. Like formulas, functions begin with the equal sign ( = ) followed by the function's name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets. Lets look at the SUM function… Lets break up the function into pieces = SUM (B1:B3) Indicates to what the function should be applicable to Indicates function/formula Indicates which function to use
Functions Lets look at the IF function… =IF(logical_test,value_if_true,value_if_false) Lets break up the IF function… = IF ( logical_test , value_if_true , value_if_false ) What the function should test? What the function should do if the test is true What the function should do if the test is false Indicates function/formula Indicates which function to use Separator Separator Indicates the endof an argument Indicates the start of an argument
Functions In the spread sheet named "formulas1" We are going to insert a IF function to test whether the cell A2 contains a value of 2 Note the IF function =IF(A2=2,"true","false") Note what the content of the cell is because the condition is true
Pivots • All the objects in the pictures rotate/swivel so that they can change weather in be vertically are horizontally in order for the object to be useful or achieve an outcome • Since Excel has horizontal rows and vertical columns this principle can be applied to spread sheets as well. How can this work for information?
Pivot tables Open the spread sheet "Amazon 2009"