Spreadsheets: Lookups, Statistics, and Visualization: Tutorial

Which type of chart or graph would be appropriate to display the annual petrol consumption of your father's car? [] scatter plot graph [] pie chart [] column chart [] bar graph [] line chart

- column chart - bar graph

Graphical Elements

A spreadsheet is a multipurpose software tool. Its functions enable you to collect and calculate types of data. You can present these calculations visually with charts and graphs in a spreadsheet. You can display the information in the form of pie charts, line graphs, and so on. Your choice depends on the data set that you're working with. Here are some of the types of charts and graphs in spreadsheet programs.

It is easier to apply LOOKUP functions using the dialog box than to use its syntax. The steps below refer to OpenOffice Calc, but these are similar to the steps in other spreadsheet programs:

1. Select a cell where you want to input the lookup value and press F2 or double-click on the cell to write in the cell. 2. Next, click the Insert option in the menu. 3. Next, click the Function option on the submenu. This option will open the Function Wizard dialog box. 4. In the Function Wizard dialog box, click the Lookup option, which will display the relevant fields. The field Search criterion refers to the value you want to find. Search vector refers to the range where you want to search, and result_vector refers to the value that the function will return. 5. Enter the relevant entries in the various fields of the dialog box and click OK.

Bar graph:

A bar graph (or horizontal column graph) displays data as rectangular bars of varying lengths. It represents variations between the individual items of a data set. The bars may be horizontal or vertical. Bar graphs are best suited to show data in sets or categories. For example, you can collect information from your friends about their favorite movies. You can then arrange the movies in genres, such as comedy, drama, horror, and so on. The most-liked category will show an increase in the number of movies in that category.

Import data from a text file:

In OpenOffice Calc, you can import a text file by using the Text Import dialog box. To transfer data from a text file to a spreadsheet in Microsoft Excel, you can either open the document using the Text Import Wizard or import the information as an external data range. Click Data menu and choose the Import External Data option. This option will open a sub-menu consisting of various options. Then, select the From Text option.

Export spreadsheet data to a database:

In OpenOffice, you can copy data from Calc to Base, provided the column structure (number of columns, order, and data-type) matches the table column structure of your database. In Microsoft Excel, you need to select the spreadsheet rows you want to copy, and then click Datasheet View option. This option will open the database file, and you can paste the copied cells into it.

Import data from a database:

In OpenOffice, you need to open the database file you want to import to Calc. The Import dBASE files dialog box appears, click OK, and the database file opens as a new Calc spreadsheet. In Microsoft Excel, you need to select the From Access option in Get External Data group. This option allows you to import a database table into a spreadsheet. Clicking this option will open a dialog box where you select the file you want to import from. Next, select the table you want to import.

Statistical Functions

Spreadsheet software provides many statistical functions that enable statistical analysis of data. These functions will help you calculate the mean, median, mode, and standard deviation of selected numbers. Spreadsheet software, such as OpenOffice Calc, Microsoft Excel, and Gnumeric, offer many useful statistical functions. Let's learn about some of these functions. Average: With the Average function, you can get the average of two or more numbers. It ignores any text input. In OpenOffice Calc, you can select up to 30 numbers to find the average of a set of numbers. Here's how you can calculate the average of numbers in OpenOffice Calc: Syntax: AVERAGE (number1; number2; number3...number30) Example: AVERAGE (2; 6; 4). The result is 4 because the average of the three numbers is 4.

Let's take a look at the steps to filter data in OpenOffice Calc. These steps more or less remain the same in other applications too.

Select the range of cells you want to filter. Next, click Data, and then click Filter. This step displays a submenu with the Advanced Filter and Standard Filter options. In the Standard Filter dialog box, specify the filter criteria. You can specify the field names and their values. Next, click OK to close the dialog box and begin data filtering. The Filter dialog box also offers different criteria options. These consist of Boolean operators (AND, OR) that you can use to specify mixed conditions for the data. When you apply a filter to a spreadsheet, you temporarily hide the rows that don't meet the filter criterion (or criteria). You can display the hidden rows again by removing the filter.

Stdev (standard deviation):

The Standard Deviation function will help you see how far a number varies, on average, from the average value of the list. In OpenOffice Calc, you can calculate it with this representation: Syntax: STDEV (number1; number2; number3...number30) Example: STDEV (8; 6; 5; 4). The result value is 1.707 because the standard deviation from the average of the numbers is 1.707.

Exporting data:

You can export data from a spreadsheet to a text file or from a spreadsheet to a database. The steps for each method appear below.

Importing data:

You can import data from a text document to a spreadsheet or from a database file to a spreadsheet. The steps for each process appear below. (Note: If the versions of Microsoft Excel or OpenOffice Calc change, then the procedure explained below may change as well).

The LOOKUP function searches for a specific value in a table.

The value can be present in a row or a column. The structure or the syntax for the LOOKUP function follows the structure given below.

Result table:

This is an array of single rows or columns. It must have the same number of elements as the search table. If the search value is in the second cell of the search table, the function will return the value corresponding to the second cell of the result table. In the table, the result table is B1:B5.

Rank:

With the Rank function, you can find a number's rank or position from a disorganized set of numbers. You can use this function for ascending or descending sets. In OpenOffice Calc, you can calculate it with this representation: Syntax: RANK(number; numberlist; order) Example: RANK(8; B1:B4), where B1, B2, B3, B4 are row numbers and have values 4, 6, 5, and 8, respectively. The value is 1 because the number 8 has a rank/position of 1 in the B column. There are many spreadsheet software products available today. Microsoft Excel is an example of a widely used spreadsheet. You can learn to use its functions if you install it in your computer and test its features.

Refer to Table 1.

You want to find out the subject taken by the student with ID of 3. You would enter the VLOOKUP formula as VLOOKUP(A4; A1:D7; 4; FALSE). The function first searches for the ID of 3 in cell A4 then returns the corresponding subject (Computer Science) from the same row

Now refer to Table 2

You want to find the Project score of a student in Semester 1. The formula will be HLOOKUP(B1; A1:C5; 4; FALSE). The function first searches for Semester 1 then returns the corresponding Project score (80) in the same column.

For example, the LOOKUP function ("A103";A1:A5;B1:B5)

will look for A103 in column A. It will display 27 as the result because A103 relates to that corresponding cell in column B.

Spreadsheets also allow:

you to add pictures and shapes. With the Insert menu in a spreadsheet, you can add pictures or shapes.

Which statistical function in a spreadsheet helps to find the most frequently occurring number within a set of numbers? The __________ function will help find the most frequently occurring number from a set of numbers.

mode

LOOKUP Functions

The LOOKUP function works like a search command in any application. It searches for a specific value within a select data range. For example, you might want to search for the enrollment date of a particular student. You know your spreadsheet stores that information in the same row as the student's name. Therefore, you can use the student's name and the column containing the enrollment date as arguments in the lookup function to obtain the specific enrollment date. The three LOOKUP functions include VLOOKUP, HLOOKUP, and Matrix LOOKUP. Use VLOOKUP, or vertical lookup, to search for a value in corresponding rows if you know the field (column). Use HLOOKUP, or horizontal lookup, when you know the value in the column of a row. The Matrix LOOKUP uses a combination of both factors. You can also use a LOOKUP function by typing the formula in a cell. Alternatively, you can use the LOOKUP dialog box if you find the syntax of the formula difficult to understand.

Line graph:

A line graph shows variations in data over a certain period of time. For example, it can show variations in body weight over 10 months. A line graph connects the values in the two axes and displays the result in the form of a line.

Scatter plot graph:

The scatter plot graph has points to relate two sets of data along the two axes. These graphs are useful to record scientific experiments to analyze varying or multiple changes. For example, you can plot the relationship between humidity in the atmosphere and temperature change.

Syntax:

VLOOKUP(lookup value; data table; column index; mode)

Match the LOOKUP functions to their formulas. Pairs 1. INDEX (array, MATCH (lookup_value, lookup_array , FALSE ) , MATCH ( lookup_value , lookup_array , FALSE) ) 2. (lookupvalue; searchtable; resulttable) 3. (lookupvalue; data table; columnindex; mode) 4. (lookupvalue; data table; rowindex; mode)

1. MatrixLOOKUP 2. LOOKUP 3. VLOOKUP 4. HLOOKUP

The steps below relate to sorting data in OpenOffice Calc. The process is more or less the same in other spreadsheet programs.

1. Select the range of cells that contain the values you want to sort. For example, if you want to sort data in cells A1 through A5, select those particular cells. 2. Next, open the Sort dialog box using Data > Sort. In the image, you can see that the Sort dialog box contains two sections: Sort Criteria and Options. You can reorder data in up to three columns or three rows. In the Sort Criteria tab, select the row(s) or column(s) along with the Ascending or Descending option, and then click OK. If you select the entire worksheet, the Sort feature sorts the first row or column, keeping the related fields of that row or column together while sorting it according to the selected criteria. 3. In the Options tab, specify criteria like column headers, case sensitive data, and so on. Usually when you sort data, the spreadsheet program overwrites the existing data in the cells with the newly sorted data. Use the Copy sort results to... option to prevent this type of overwrite. It copies the sorted data to another specified location.

While sorting a spreadsheet, if you select the option "Copy sort result to..." how does the spreadsheet data change? []It copies the sorted data to the specified location. []It sorts the original data. []It deletes the original data. []It keeps the original data unchanged

1st 4th

Column chart:

A column chart uses multiple columns. With this chart, you can visually compare data that's displayed as columns. It shows variations between items. For example, a column chart is ideal to compare the variations in protein content in edible items. The elements of a column chart are the column length, font, color, and so on.

Pie charts:

A pie chart displays the different sections of any data as parts of a pie. For example, you may need to display the number of people who speak different languages in a country. You can present this data in a pie chart. It will help display the percentage of speakers who use each language. The elements of a pie chart are the arc length of the pie, font, color, and so on.

Creating Charts

Different spreadsheet applications follow different methods to add charts and data. Here's how to create a chart in OpenOffice Calc: 1. Click Chart from the Insert menu. Choose the required chart type. 2. Then, choose the Data Range you want to present in the chart. You can use a series of rows or columns. After setting the data range, click Next. 3. You can use various options to tune the selected data. The Data Series option will enable you to add more details to the spreadsheet data. The Chart Elements option will enable you to give the chart a suitable title and subtitle. 4. You can give an appropriate title to your chart under the Chart Elements option. If required, add a subtitle. You can select the axis (X or Y) on which to display the data. Chart Elements also gives you the option to show grids on either axis. 5. You can change the chart's elements, layout, and styles from the spreadsheet's Format menu. Then, choose whether to use a legend, header, or footer. If you use a legend, you can select where it should appear.

syntax:

LOOKUP(lookup value; search table; result table)

Importing and Exporting Data

Spreadsheet applications provide various options that allow you to easily import and export spreadsheet data. The process may involve pasting data from one spreadsheet to another or converting the selected data into a format supported by the spreadsheet application. These processes help users to share data in any form or to present it to others without requiring it to be in the form of a spreadsheet. Let's explore the main steps in importing and exporting data.

more info:

Sorting and filtering data in a spreadsheet can help you analyze and view data in more than one way. You can sort data when you need to view it in a specific order. For example, you might maintain a spreadsheet of student names and their scores on class tests, and you want to see a list of students' scores from highest to lowest. To view the scores, you can sort the scores column in descending order. Another example of sorted data is a glossary. In a glossary, you can arrange terms in alphabetical order. This order makes it easier for readers to look for a specific term and its associated meaning. Filtering is similar to sorting. Imagine that you maintain a list of various books. These books belong to different genres, such as humor, satire, fiction, and so on. Use a filter when you want to view books of only one specific genre, such as fiction. Filtering will hide all other book rows, while displaying all rows related to fiction books.

Min (minimum):

The Minimum function expresses the minimum value in a selected set of data. In OpenOffice Calc, you can calculate it with this representation: Syntax: MIN(number1; number2; number3....number30) Example: MIN(1; 2; 2). The result value is 1 because it is the lowest number in the given set.

Median:

The median value is the middle number in a sorted set of numbers. A sorted set is a series of numbers arranged in ascending or descending order. In OpenOffice Calc, you can calculate the median with this representation: Syntax: MEDIAN (number1; number2; number3...number30) Example: MEDIAN (1; 2; 5; 6; 7; 8; 9). The result value is 6 because it is the middle arithmetic value. Mode: The Mode function will express the most frequently occurring number in a given set. In OpenOffice Calc, you can calculate the mode with this representation: Syntax: MODE (number1; number2; number3...number30) Example: MODE (2; 3; 4; 5; 3; 3). The result value is 3 because it repeats itself the most number of times.

Matrix LOOKUP:

This function searches for a lookup value that exists at the intersection of rows and columns. This means that the command is a combination of the vertical and horizontal lookups. The syntax to write this function is: INDEX(array, MATCH (lookup_value, lookup_array, FALSE), MATCH(lookup_value , lookup_array, FALSE)) The syntax includes an INDEX function, which consists of two MATCH functions. The first MATCH function searches for the lookup value specified in the first column and returns its row number in the first column. The second MATCH function searches for the lookup value specified in the first row and returns its column number in the first row. Let's look at an example. The table shows you grades of students across four years. You want to know the grade of Student 3 in 2011. Therefore, you would write: INDEX(A2:E6, MATCH (A5, A2:A6, FALSE), MATCH(C2,A2:E2, FALSE)) A2:E6 is the entire array. The cell A5 in the range A2:A6 is where you will find Student 3. The cell C2 in the range A2:E2 is where you will find 2011. The output is the intersection of the row and column, which is the grade B.

Search table:

This refers to the search range, which must be an array of single rows or columns. In the table, cells A1 to A5 (A1:A5) form the search table.

Lookup value:

This refers to the value you want to look up. This value may be logical (TRUE or FALSE), text, or a number stored in the search table.

Export spreadsheet data to a text file:

To export spreadsheet data to a text file in either OpenOffice Calc or Microsoft Excel, use the Save As option to export the data. A dialog box will appear, allowing you to convert the spreadsheet to a compatible format. After you convert the spreadsheet, you can export it into a text document.

Sorting and Filtering Data in Spreadsheets

You may not use any particular order when you enter data in a spreadsheet. However, to analyze the data, you might want to organize it in a specific order or structure. You can use sorting and filtering techniques to organize data based on your specific needs. You can sort text in alphabetical order, numbers in order of magnitude, or dates in chronological order. In each case, you can rearrange the data in either increasing order or decreasing order. For example, sorted text can appear from "a to z" or "z to a". You can also sort numbers from smallest to largest or the reverse. In filtering, the spreadsheet retrieves specific values based on your criteria and lists them. While sorting rearranges all data in the spreadsheet, filtering hides rows of data that do not match the specified criteria.

Which function will allow you to transfer data from a text document to a spreadsheet? You can ___________ data from a text document to a spreadsheet.

import

VLOOKUP

is a function that searches a column and a single row for the value specified. It starts the search process from the top-left column in the argument's data table. The lookup value is the reference in the table you want to search. The column index is the column number where the result is stored. The argument's mode accepts two values; FALSE or TRUE. If the mode is FALSE, it finds an exact match. If the mode is TRUE, it finds an approximate match.

HLOOKUP

is similar to VLOOKUP except that it searches by rows instead of columns. It will search for a value starting from the top row specified in the argument's data table. The row index is the row number where your result is stored. Similar to the VLOOKUP function, HLOOKUP accepts TRUE and FALSE arguments to determine whether to find an approximate or exact match. Syntax: HLOOKUP(lookup value; data table; row index; mode)