FORMULAS AND FUNCTIONS IN EXCEL Mrs. Brownfield Smith-Cotton High School Sedalia, MO
FORMULAS • A mathematical operation performed in a spreadsheet • Can be entered directly in the cell • Can be entered in the formula bar
FORMULAS • All formulas must start with the equal sign = • Then add your mathematical operation • Type enter to add the formula in the desired cell
FORMULAS • Make sure cell D3 is selected • Always start with the = • Then add the cells and operators • =A4/(A1+B3+C2) Example: Enter a formula in cell D3 that divides cell A4 by the sum of cells A1, B3, and C2.
FUNCTIONS • Also mathematical operations • Specific to an operation needed • Examples: • SUM (adds) • AVG (averages) • MIN (Finds minimum number) • MAX (Finds maximum number)
FUNCTIONS • Click to make cell A8 active • Type AVERAGE • Click to make cell C8 active • Type the = • Type AVERAGE(C2:C6) • AVG is the function you want to perform • (C2:C6) is the range you want to apply the function to
IF FUNCTION • Logical Test • Example: If it rains, I will take my umbrella • If I have $20 I will save 10%, but if I have more than $20 I will save 15%
IF FUNCTION How does this work in Excel? If I have $20 I will save 10%, but if I have more than $20 I will save 15% Step 1 – Click to activate cell C2
IF FUNCTION Step 2 – Go to INSERT - FUNCTION Step 3 – Click on IF and Click OK
IF FUNCTION Step 4 – Enter your test and answers If B2 is less than or equal to 20, then multiply B2 by 10%, if not, multiply B2 by 20%
IF FUNCTION • Step 5 – Your formula should be entered, now copy it to cells C3 through C8 • Click and hold the tiny black box (Fill Handle) • Drag it down to the bottom of C8 and let go
IF FUNCTION Your final product should look similar to this! Yeah, it calculated the savings for you!
FORMULAS & FUNCTIONS • Formulas • Mathematical operation • Always start with the equal sign • Follow order of operations • Functions • Specific mathematical operation • Always start with the equal sign • Use function command, parenthesis, and cell ranges