1 / 15

Functions

Learn about basic spreadsheet functions such as VLOOKUP, IF function, and logical operators. Discover how to use these functions to process data efficiently in spreadsheet software.

jeffe
Download Presentation

Functions

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Functions

  2. This lesson will cover: • Basic spreadsheet functions. • Finding data using VLOOKUP. • Using the IF function and other logical operators.

  3. Functions Spreadsheet software comes with a variety of different functions that can make it easy to process data efficiently. What are functions and what do they do?

  4. Simple functions Typing out a formula like would eventually be tiresome, especially if lots more cellsneeded to be added into the equation. To make this easier Excel has functions which will help makewriting formulae easier. For example: • Sum adds together a series of numbers • Average works out the average of a series of numbers • Max works out the largest of a series of numbers • Min works out the smallest of a series of numbers. Functions also use the colon (:) to stand for all the cells in between two cells. So that becomes

  5. Using functions

  6. Finding a record VLOOKUP allows you to search through a table of information and find the relative value. • Like a database, the table has: • field names – Subject, Teacher, Room • records – each row is a different record. For example, the first record is ‘English Mrs Ridyard Room 6’. To find out the ICT teacher, you would look down the Subjectcolumn until you found ICT, then across to the Teachercolumn to find the teacher’s name.

  7. VLOOKUP The computer does just the same. The function which is entered in cell B7 is as follows: =VLOOKUP(A7,A2:C5,2,FALSE) – Mrs Spencer Vertical (i.e. column) lookup (find) Find the word in cell A7 Look between A2:C5 Take the value in column 2 Find anexact match

  8. TRUE and FALSE In VLOOKUP, a TRUE value is used when searching for numbers and wanting to find the closest match. For example, you might look up the value ‘5’, but still want data returned where the value is ‘5.1’. However, the previous example required an exact match of letters so FALSE would be used. VLOOKUP(A7,A1:C5,2,FALSE), TRUE and FALSE are used in many formulae. They are logicalvalues like ‘1’ and ‘0’ or ‘Yes’ and ‘No’. The effect they have depends on how the formula is constructed.

  9. VLOOKUP

  10. Logical operators in spreadsheets

  11. More on the IF function The IF function is one of the most commonly used functions as it allows you to create logical statements. Logical test Value if true Value if false =IF(D1<26,100,200 ) If the cell D1 is less than 26 then return the value ‘100’ in the cell. If the cell D1 is greater than 26 then return the value ‘200’ in the cell. IF statements can be used to alert you when tolerances are breeched (e.g. going over budget) or they can be combined with other statements like COUNTIF.

  12. Functions

  13. Formulae in spreadsheets

  14. Correcting formulae and functions

  15. Revise those functions

More Related