Loading in 5 sec....

Formulas and Functions 1PowerPoint Presentation

Formulas and Functions 1

- 65 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about ' Formulas and Functions 1' - temira

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Getting Familiar with Microsoft Office 2010: Excel

- The Ribbon
- Laid out with multiple Tabs
- File, Home, Insert, Page Layout, Formulas, Data, Review, View

- Each tab has multiple Groups of Functions
- Example: Home Tab
- Groups: Clipboard, Font, Alignment, Number, Styles, Cells, Editing

- Example: Home Tab

- Laid out with multiple Tabs

Common Formulae

- Basics in the Sigma on Home and Functions tab
- Sum
- Avg
- Count
- Max
- Min
- More

- Uses
- SUM: totals a row/column
- Avg: averages selection
- Count: counts the number of entries in a selection
- Max & Min: returns the Max or Min value from selection
- More: opens the insert function wizard

Example #1: the budget of expenses

In this exercise we will download and edit an existing spreadsheet. The goal of this exercise is to get familiar working in the Microsoft Office 2010: Excel environment. This spreadsheet is a simple budget of household expenses. We will use the most common functions:

Sum, Avg, Min, Max

We will also write a custom formula while using some of the nifty built-in features of Excel. Finally, we will add a pie-chart based on our custom formula.

Formulas Tab

- Financial
- Logical
- Text
- Date & Time
- Lookup & Reference
- Math & Trig
- More

- We will only use a few of these for the next example

Date & Time

- Unless you are Kim Peak, you would need this to find days of the week in other years
- Great for setting work schedules
- Excel has trouble with dates before 1900

- Networkdays: the number of standard work days between two dates
- Now: the time NOW!
- Date: gives the date in format
- eDate: gives the date in Excel serial format

Text

- Used for large blocks of text
- Same logical tests as computer programming
- Has functions to deal with funny formatting

- Concatenate
- Search
- Find
- Replace
- Substitute
- Dollar
- Value

Logical

- The series of logical mathematic tests
- If, Not, And, Or, True, IfError, False
- Very useful in creating statements that check for values
- Used in making an automatic grading structure

- And: true if both statements true
- Or: true if both statements are not false
- Not: reverses value
- If: checks for value and returns input value
- IfError: returns input if the statement errors
- True/False: returns that

Lookup & Reference

- Used to find data in large spreadsheets
- Has indexing and database options for organizing data, and useful attributes for Visual Basic coding

- Hyperlink: adds a hyperlink
- Transpose: takes a vertical array and transposes it horizontally
- GetPivotData: extracts from pivot table

Example #2: the Gradebook

This exercise covers more of the formulas included in Excel. Here we will use formulas from Date & Time, Text, Lookup & Reference, and Logical.

We will also use View tools to help examine the spreadsheet, and some formatting options to make it more visually appealing.

Download Presentation

Connecting to Server..