Explore Microsoft Excel’s ability to import and summarize data

Sponsored Links

we will explore Microsoft Excel’s ability to import and summarize data using the following twelve different functions: 

  • IMPORT
  • LIST
  • SORT A LIST
  • SUBTOTALS
  • COUNT
  • COUNTA
  • COUNTIF
  • SUMIF
  • AVERAGE
  • MEDIAN
  • MAX
  • MIN

IMPORT


Sponsored Links

Microsoft Excel can import data from external data sources such as an AS400, the internet, or an external database. Examples of data that we have imported and manipulated include fixed assets, accounts receivable, inventory, sales by customer, etc.

Start by simply exporting the data from it source program in one of the following formats:

        – TXT (“Text”)

 

        – CSV (“Comma Separated Variables”)

 

      – DIF (“Data Interchange File”)

Then open the file like a normal Excel file, the “Text Import Wizard” will guide you through importing the data. Nearly all files are comma delimited. The result spreadsheet is called a “list”.

LIST


A LIST is a series of worksheet rows that contain related data, such as an invoice database or a set of client names and phone numbers. A list can be used as a database, in which rows are records and columns are fields. The first row of the list has labels for the columns.

SORT A LIST


You can rearrange the rows or columns of a list based on the values in the list by sorting. When you sort, Microsoft Excel rearranges rows, columns, or individual cells by using the sort order that you specify. You can sort lists in ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) order, and sort based on the contents of one or more columns.

Microsoft Excel sorts lists alphabetically by default. If you need to sort months and weekdays according to their calendar order instead of their alphabetic order, use a custom sort order. You can also rearrange lists in a specific order by creating custom sort orders. For example, if you have a list that contains the entry “Low,” “Medium,” or “High” in a column, you can create a sort order that arranges rows that contain “Low” first, rows that contain “Medium” next, and rows with “High” last.

To perform a sort highlight the entire data range you would like to sort. Then from the toolbar select “Data” and “Sort”. Select the rows to sort and the order desired ascending or descending. If the list does not have a header row select, check “No Header Row”

Sponsored Links

SUBTOTALS


Insert subtotal values into a list

In a long list of data, you can use subtotals to automatically outline the information on a worksheet. You can “nest,” or insert, subtotals for smaller groups within subtotal groups, which is useful when you need to calculate subtotals within groups of values. You can remove subtotals from a list without affecting your original data.

      1 – Sort the list by the column for which you want to calculate subtotals. For example, to summarize the units sold by each salesperson in a list of salespeople, sales amounts, and the number of units sold, sort the list by the salesperson column.
        2 – Click a cell in the list.

 

        3 – On the Data menu, click Subtotals.

 

        4 – In the At each change in box, click the column that contains the groups for which you want subtotals. This should be the same column by which you sorted your list in step 1.

 

        5 – In the Use function box, click the function you want to use to calculate the subtotals.

 

        6 – In the Add subtotal to box, select the check boxes for the columns that contain the values for which you want subtotals.

 

COUNT


Counts the number of cells that contain numbers and numbers within the list of arguments. Use COUNT to get the number of entries in a number field in a range or array of numbers.

Syntax

COUNT(value1,value2, …)

    “Value1”, “value2”, … are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. If you need to count logical values, text, or error values, use the COUNTA function.

Examples

In the following example, suppose A1:A7 contain “Sales”, “12/8/90”, “”, “19”, “22.24”, “TRUE”, “#DIV/0!” respectively:

COUNT(A1:A7) equals 3
COUNT(A4:A7) equals 2
COUNT(A1:A7, 2) equals 4

COUNTA


Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax

COUNTA(value1,value2, …)

    “Value1”, “value2”, … are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text (“”) but not including empty cells. If an argument is an array or reference, empty cells within the array or reference are ignored. If you do not need to count logical values, text, or error values, use the COUNT function.

Examples

In the following example, suppose A1:A7 contain “Sales”, “12/8/90”, “”, “19”, “22.24”, “TRUE”, “#DIV/0!” respectively:

COUNTA(A1:A7) equals 6
COUNTA(A4:A7) equals 4
COUNTA(A1:A7, 2) equals 7
COUNTA(A1:A7, “Two”) equals 7

COUNTIF


Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range,criteria)

“Range” is the range of cells from which you want to count cells.

“Criteria” is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, “32”, “>32”, “apples”.

Examples

Suppose A3:A6 contain “apples”, “oranges”, “peaches”, “apples”, respectively:
COUNTIF(A3:A6,”apples”) equals 2
Suppose B3:B6 contain 32, 54, 75, 86, respectively:
COUNTIF(B3:B6,”>55″) equals 2

SUMIF


Adds the cells specified by a given criteria.

Syntax

SUMIF(range,criteria,sum_range)

“Range” is the range of cells you want evaluated.

“Criteria” is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, “32”, “>32”, “apples”.

“Sum_range” are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

Example

Suppose A1:A4 contain the following property values for four homes: $100,000, $200,000, $300,000, $400,000, respectively. B1:B4 contain the following sales commissions on each of the corresponding property values: $7,000, $14,000, $21,000, $28,000.

SUMIF(A1:A4,”>160000″,B1:B4) equals $63,000

AVERAGE


Returns the average (arithmetic mean) of the arguments.

Syntax

AVERAGE(number1,number2, …)

    “Number1”, “number2”, … are 1 to 30 numeric arguments for which you want the average.

Remarks

      The arguments must be either numbers or names, arrays, or references that contain numbers.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Tip: When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Zero values check box on the View tab (Options command, Tools menu). Empty cells are not counted, but zero values are.

Example

If A1:A5 is named Scores and contains the numbers 10, 7, 9, 27, and 2, then:

AVERAGE(A1:A5) equals 11
AVERAGE(Scores) equals 11
AVERAGE(A1:A5, 5) equals 10
AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5) equals 11

If C1:C3 is named OtherScores and contains the numbers 4, 18, and 7, then:

AVERAGE(Scores, OtherScores) equals 10.5

MEDIAN


Sponsored Links

Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.

Syntax

MEDIAN(number1,number2, …)

      “Number1”, “number2”, … are 1 to 30 numeric arguments for which you want the median.

The arguments should be either numbers or names, arrays, or references that contain numbers. Microsoft Excel examines all the numbers in each reference or array argument.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Remarks

    If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second example following.

Example

MEDIAN(1, 2, 3, 4, 5) equals 3

MEDIAN(1, 2, 3, 4, 5, 6) equals 3.5, the average of 3 and 4

MAX


Returns the largest value in a set of values.

Syntax

MAX(number1,number2,…)

“Number1”, “number2”,… are 1 to 30 numbers for which you want to find the maximum value.

      You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text must not be ignored, use MAXA instead.

If the arguments contain no numbers, MAX returns 0 (zero).

Example

If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:

MAX(A1:A5) equals 27
MAX(A1:A5,30) equals 30

MIN


Returns the smallest number in a set of values.

Syntax

MIN(number1,number2,…)

“Number1”, “number2”,… are 1 to 30 numbers for which you want to find the minimum value.

      You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored.

If the arguments contain no numbers, MIN returns 0 (zero).

Example

If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:

MIN(A1:A5) equals 2
MIN(A1:A5,0) equals 0