Formulas and Functions

Formulas and Functions An Introduction

Formula Basics • All formulas begin with “=“ • Simple/common formulas use: • * for multiplication • / for division • + for addiction • - for subtraction

Order of Operations • Parenthesis or brackets • Do the math in the parenthesis according to the order of operations before moving on to outside the parenthesis • Multiplication and division • Addition and subtraction

Getting Started with Formulas • Select Formula from the toolbar and select Insert Function • Choose the function from the dialogue box that appears • Enter the desired cells for the function • Useful functions: • http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx

sum • Adds up large numbers to get a total

now • =NOW() • Time and date stamps • Don’t forget the () at the end, for today

date • Enters the date for any given day • Important when using dates in other formulas • =DATE(year, month, day)

yearfrac • Shows the fraction of days between two dates • Important: use the date function to enter dates! See previous slide • =Yearfrac(start_date, end_date)

CONCATENATE • Joining different texts into 1 sentence. • How? • =CONCATENATE(A1, “ “ , “ate“, “ “ , B1, “ “ , C1) • Make sure to include spaces! • “”’s around spaces and words you insert Notice the spaces?

Separating first and last names • This will help you separate the first and last names on a long list, into separate cells. • First, highlight the list and select “Text to columns” in the data dropdown menu Then, click next.

Select the separator you want. In this example, we’re using a comma.

Next, select a destination (cell) to start you new list, and click finish. This: Becomes this:

Conditional Formatting • Helps organize data • First, input your information: Then, highlight it:

Choose “Conditional Formatting” in the toolbar” Next, you can choose to use bars, colors, or icons to organize the data:

Color Scale: Icons:

Rules • You can edit the rules for conditional formatting, by clicking on “manage rules” in the dropdown menu.

Here you can change the values for the different colors or icons, and many other options.

Formulas and Functions