Formulas and functions 1
1 / 10

Formulas and Functions 1 - PowerPoint PPT Presentation

  • Uploaded on

Formulas and Functions 1. Microsof t Office 2010 Excel Seminar Xander Jackson. 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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
Formulas and functions 1

Formulas and Functions 1

Microsoft Office 2010

Excel Seminar

Xander Jackson

Getting familiar with microsoft office 2010 excel
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

Common formulae
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
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
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
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

Formulas and functions 1

  • 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


  • 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
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
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.