Analytics on Spreadsheets-converted.pptx

tableau

1. Analytics on Spreadsheets

2. Why Microsoft Excel? Many commercial software packages, business intelligence suites and spreadsheets can be used for Business Analytics Pentaho, SAS, MS Excel Microsoft Excel has the basic features of all spreadsheets. It has functions to answer statistical, engineering and financial needs display data as line graphs, histograms and charts view data from different perspectives programming aspect, Visual Basic for Applications decision support system (DSS), via a custom-designed user interface connect to external data sources export the data in various format Microsoft Excel is widely used across all areas of business.

3. Cell References Cell references can be relative or absolute. Using a dollar sign before a row or column label creates an absolute reference Relative references: A2, C5, D10 Absolute References : $A$2, $C5, D$10 Which cell referencing is used makes a critical difference when we copy the cell formula.

4. Excel Formulas Formulas in Excel use common mathematical operators: Addition (+) Subtraction (-) Multiplication (*) Division (/) Exponents (^)

5. Copying Formulas Cells can be copied in many ways Use the Copy button in the Home tab, then Paste Use Ctrl-C, then Ctrl-V Drag the bottom right corner of a cell (the fill handle) across a row or column Double click on the fill handle of a cell and its value (or formula) is copied to the cells below if there is data in an adjacent column.

6. Basic Excel Functions MIN(range) – finds the smallest value in a range of cells MAX(range) – finds the largest value in a range of cells SUM(range) – finds the sum of values in a range of cells AVERAGE(range) – finds the average of the values in a range of cells COUNT(range) – finds the number of cells in a range that contain numbers COUNTIF(range, criteria) – finds the number of cells within a range that meet a specified criterion.

7. Using Basic Excel Functions In P urc h as e O r de r d a t as e t w e wi l l f i n d the following: Smallest and largest quantity of any item ordered Total order costs A v e r a g e n um b er o f m o n ths p e r o r de r f o r ac c ou n t s payable. Number of purchase orders placed Number of orders placed for O-rings Num b er o f o r de r s with A/P t e r ms shor t er than 30 months

8. Insert Function Select a cell and click on the Insert function (fx) button in the Function Library group in the Formulas tab. Either type in function description in the search field or select a category from the drop down box. Example: COUNTIF function

9. Logical Functions IF(condition, value if true, value if false) – a logical function that returns one value if the condition is true and another if the condition is false. AND(condition 1, condition 2…) – a logical function that returns TRUE if all conditions are true and FALSE if not. OR(condition 1, condition 2….) – a logical function that returns TRUE if any condition is true and FALSE if not. Conditions may include equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=) and not equal to (<>) Include Order Size field. Order size of 10,000 units or more is considered as large whereas any other order size is considered to be small.

10. Lookup Functions To find specific data in a spreadsheet: VLOOKUP(lookup_value,table_array,col_index_num) : in leftmost column of a table and returns a value in the same row from a column you specify. Table sorted in ascending order. HLOOKUP(lookup_value,table_array,row_index_num): looksup a value in the top row of a table and returns a value in the same column from a row you specify. INDEX(array, row_num,col_num)returns a value or reference of the cell at the intersection of a particular row and column in a given range. MATCH(lookup_value, lookup_array, match_type) returns the relative position of an item in an array that matches a specified value in a specified order.

11. E x amples In Sales Transactions Excel file ,find the transaction code and payment type for customer ID 10005 . Find the transaction code for customer id -10007. Find whether transaction code 10001 exist in the file.