Mathematical Formulas and Excel Formulas and Functions Formulas

Mathematical Formulas and Excel Formulas and Functions

Formulas �The real power of spreadsheets. �Automatically calculates math, based on formulas that you input into specific cells. �What can you include in these formulas? �Addition (+) �Subtraction(-) �Multiplication(*) �Division(/) �Exponents (^)

Formulas �Indicator �the first sign in a spreadsheet formula �In excel we always start formulas with an equal sign (=) �Syntax �Formulas must follow a specific sequence in order to work properly. � Excel: Follows the BEDMAS rule � Brackets, Exponents, Division, Multiplication, Addition, Subtraction

Getting Used to Formulas �To add numbers together �Type Cell Locations, NOT values in the cell you are working in where the answer should appear. � i. e. =B 3 + A 1 + B 2 �To Multiply or Divide �Cells are separated by either * or / � i. e. = (B 3*A 3)/4 � Excel will calculate the multiplication in the brackets first, then divide the answer by 4.

Functions in Excel �Using a function instead of a formula will make the input of a formula much faster. �Functions are pre-formatted mathematical demands that indicate to the program what calculations need to be done. �Instead of entering every individual cell – you can insert a function and select a range of cells. �Example: Instead of the formula = A 1+A 2+A 3+A 4 We use the function = SUM(A 1: A 4)

Spreadsheet Functions �Frequently Used Spreadsheet Functions Ø SUM – calculates the total of a range of cells Ø MIN- identifies the lowest value (minimum) in the range of values specified Ø MAX – identifies the highest value (maximum) in the range of values specified Ø AVERAGE or AVG – calculates the arithmetic mean of the values in the specified cells Ø COUNT – counts the number of numeric values in the range specified

What makes up a formula with a function? 1) The indicator (=) 2) The function name (e. g. SUM, AVG) 3) The arguments (contained within brackets) – the cells on which you want the function performed FUNCTION =AVG(B 3: B 14) INDICATOR ARGUMENT

Inserting Functions �Click on the fx button next to the formula bar. �Select the function you wish to insert. �You may have to search for it using the search bar. �A box will appear that will allow you to select your cells/range. �Click ok when you are finished. �Non consecutive cells are separated by a comma (, ) �Consecutive cells are separated by a colon (: )

Fill Down and Fill Across �This allows you to take a formula or data and replicate it down an entire column or across an entire row. �Absolute Cell Referencing: Will keep a certain cell constant while changing others. � Insert $ before both column and row reference ($A$1) that you would like to keep the same when you fill down or across. �Relative Referencing: Will replicate a value down a column or across a row and change the values relative to the cell that you are copying too.

Tips �Pressing CTRL + ~ allows you to toggle between viewing your values and formulas/functions in a spreadsheet. �ESC will allow you to exit out of inserting a formula/function if you have made an error. �Double clicking on the seam of a column or row will automatically adjust it to be the width of the information in the cell.

Common Errors in Excel Formulas �#### means the column is too narrow for the values. �#NAME? means a cell name is incorrect (ie. AQ instead of A 3). �#REF! means a cell you are referring to in a calculation has likely been deleted. �#VALUE! means a cell you want to use in a formula is probably a label.