Microsoft Excel Chapters 2 3 nagendra vemulapallimail wvu

Microsoft Excel Chapters 2 &3 nagendra. vemulapalli@mail. wvu. edu Nagendra Vemulapalli

Announcements • Homework assignment 1 is posted in the website http: //cs 101. wvu. edu/instructors/vemulapalli/a ssignments/ • It is due on 09/14/2012 by 11: 59 pm • Lesson B in My. ITLab is due on 09/17/2012 by 11: 59 pm

Logical Functions • IF function – IF(logical_test, value_if_true, value_if_false) • Logical Operators Operator Description = Equal to <> Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to 12/14/2021 Nagendra Vemulapalli 3

Range Names • Create a range name – Click in the Name Box, type the range name, and then press Enter. – Click the Formulas tab, click Define Name in the Defined Names group to open the New Name dialog box, type the range name in the Name box, and then click OK 12/14/2021 Nagendra Vemulapalli 4

Rank. Function • The RANK function is useful for producing a ranking by using a formula • Syntax =RANK(number, range, order) number: the number/cell for which you want to find the ranking within the “range” range: range of numbers/cells within which the “number” will be ranked Order: not really required for our purposes. Leave it blank do not put anything.

Rank Function Example • Open Example 2 file in Day 4’s lecture notes • We want to find the Rankings for the values in column 3 among the same column • Insert a Rank function in cell D 3 • Type =rank(c 3, c 3: c 8) • Autofill the formula to remaining cells. Observe the cell references and make the references absolute • =rank(c 3, $c$3: $c$8)

Conditional Formatting • Formats the data in excel sheet if and only if the data satisfies the condition. • We wish to apply conditional formatting for the values in column 3 • Select all the values in column 3 ->Click on conditional formatting in styles group ->Select the type of condition which you want to give ->Give the condition

Imports • Importing is the process of inserting data from one application or file into another. • A text file is a data file that contains letters, numbers, and symbols only. – A delimiter is a character used to separate data in a text file.

Importing a Text File • ->Data Ribbon ->Click “from text” which is in External data group ->Browse the location of text file ->Open the text file • Text import wizard (3 steps) – Select the type that describes the data – Select the type of delimiter in your data – Select the format of column data – Select the location (where to import the data in your sheet)

Importing an Access Database Table or Query • ->Data ribbon ->Click “from access” in External data group ->Browse the location of file ->Open the file • Import Wizard – Select the table in database which you want to import into your sheet – Select the location where you want to place the imported data

Imports • Import data from other sources Source Definition SQL Server Create a connection to an SQL server table and import data as a table or Pivot. Table. Analysis Services Create a connection to an SQL server analysis services cube, and import data as a table or Pivot. Table XML Data Import Open or map an XML file into Excel Data Connection Wizard Import data for an unlisted format by using the Data Connection Wizard and OLEDB. Microsoft Query Import data for an unlisted format by using the Microsoft Query Wizard and ODBC.

Text Manipulation • In the imported data, the data may not be structured in a way that meets your needs. • Converting text to columns. – Select the cell or range which contains the delimited text – Go to Data ribbon->Data tools->Text to columns • Manipulating Text with functions. – Concatenate Function combines the text =CONCATENATE(text 1, text 2) – Proper Function capitalizes the first letter in a text string =PROPER(text) – Upper Function converts the text to upper case =UPPER(text) – Lower Function converts all upper case letters to lower case =LOWER(text)

Text Manipulation • Other text functions Function Description TRIM(Text) Removes leading and trailing spaces in a text string but maintains spaces between words in a text string LEFT(Text, Num_chars) Returns the specific number of characters from the start of a text string RIGHT(Text, Num_chars) Returns the specified number of characters from the end of a text string MID(Text, Start_num, Numchars) Returns the specified number of characters from the middle of a text string, based on a starting position and length

XML • e. Xtensible Markup Language (XML) is an industry standard for structuring data that enables data to be shared across applications, operating systems, and hardware • Importing XML data into Excel

Importing XML data • ->Data ribbon ->Click “From other sources” in Get external data group ->Click “From xml data import” ->browse the location of xml file ->Open the file ->Select the location where you want to place the imported data in your Excel sheet