Excel and Advanced Excel with stat and Analysis

Advanced Excel programming

outcomes 2 EXCEL FOUNDATION ADVANCED EXCEL TECHNIQUES DATA ANALYSIS WITH EXCEL VBA EXCEL PROGRAMMING

Data analyst tools 4 python Advanced Excel R SQL Open Tools Tableau ….

Foundation and Basics Cell Cell Alignment Row Height and width Merge and center Data Analysis using EXCEL

Formatting cells Number Date Currency Accounting Fraction Text to Numbers Numbers to Text Data Analysis using EXCEL

Enter the value in c2 cell Data Analysis using EXCEL

Right click and open format cells Data Analysis using EXCEL

Data Analysis using EXCEL

Increase / Decrease decimal vlues Data Analysis using EXCEL

Fractions Data Analysis using EXCEL

Text to Numeric Data Analysis using EXCEL

Cell address Referencing Relative Absolute Mixed Data Analysis using EXCEL

Relative Data Analysis using EXCEL

Absolute 1cm= 0.393701inches Data Analysis using EXCEL

3d reference Data Analysis using EXCEL

Sort & Filtering Sort Custom sort Filter Advanced Filter Data Analysis using EXCEL

Advanced Filter Data Analysis using EXCEL

Data Analysis using EXCEL

Advanced Filter Comparison operator Meaning Example = (equal sign) Equal to A1=B1 > (greater than sign) Greater than A1>B1 < (less than sign) Less than A1<B1 >= (greater than or equal to sign) Greater than or equal to A1>=B1 <= (less than or equal to sign) Less than or equal to A1<=B1 <> (not equal to sign) Not equal to A1<>B1 Data Analysis using EXCEL

Advanced Filter Example Multiple criteria, one column, any criteria true Salesperson = "Davolio" OR Salesperson = "Buchanan" Multiple criteria, multiple columns, all criteria true Type = "Produce" AND Sales > 1000 Multiple criteria, multiple columns, any criteria true Type = "Produce" OR Salesperson = "Buchanan" Multiple sets of criteria, one column in all sets (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) Multiple sets of criteria, multiple columns in each set (Salesperson = " Davolio " AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) Data Analysis using EXCEL

Mathematical and Date Functions PRODUCT SUM COUNT MIN MAX DATE TIME DATEDIF TODAY Data Analysis using EXCEL

Logical operations If function IF(TEST,TRUE,FALSE) AND Sumif countif Data Analysis using EXCEL

Statistical functions Mean Data Analysis using EXCEL

Variance: Data Analysis using EXCEL

Standard Deviation: Data Analysis using EXCEL

Co variance: Data Analysis using EXCEL

Correlation coefficient Data Analysis using EXCEL

VLOOKUP HLOOKUP The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify. Data Analysis using EXCEL

Data Analysis using EXCEL

Activity Banking Payroll Merchandise Trade Data Analysis using EXCEL

Pivot Table Data Analysis using EXCEL

Data Analysis using EXCEL

Charts and Graphs Data Analysis using EXCEL

Data Analysis using EXCEL

Change Chart Type 1. Select the chart. 2. On the Design tab, in the Type group, click Change Chart Type. Data Analysis using EXCEL

Data Analysis using EXCEL

Scatter plot Data Analysis using EXCEL

Data Analysis using EXCEL

Histogram Data Analysis using EXCEL

correlation Data Analysis using EXCEL

Descriptive stat Data Analysis using EXCEL

Rank and percentile Data Analysis using EXCEL

Spark Line Data Analysis using EXCEL

What if Analysis What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. Data Analysis using EXCEL

Scenarios Data Analysis using EXCEL

LOADING DATA FROM EXTERNAL SOURCES Loading the Data from a Text File Loading the Data from web Exporting Data to the Text File Transferring the Data from Micro soft Excel to Micro Soft PowerPoint Data Analysis using EXCEL

Anova Analysis of Variance(ANOVA) helps you test differences between two or more group means. ANOVA test is centered around the different sources of variation (variation between and within group) in a typical variable. A primarily ANOVA test provides evidence of the existence of the mean equality between the group. This statistical method is an extension of the t-test. It is used in a situation where the factor variable has more than one group. Data Analysis using EXCEL

This example teaches you how to perform a single factor ANOVA (analysis of variance) in Excel. A single factor or one-way ANOVA is used to test the null hypothesis that the means of several populations are all equal. if F > F crit , we reject the null hypothesis. If p value > .10 → “not significant” If p value ≤ .10 → “marginally significant” If p value ≤ .05 → “significant” If p value ≤ .01 → “highly significant.” Data Analysis using EXCEL

Two Way ANOVA in Excel 2013: with Replication If the F-value ( f )is larger than the f critical value ( f crit ) If thep -value is smaller than your chosen alpha level. Data Analysis using EXCEL

VBA VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs. Data Analysis using EXCEL

ENABLE DEVELOER Data Analysis using EXCEL

Data Analysis using EXCEL

Range Object Data Analysis using EXCEL

Variables Starts with keyword Dim Dim x As Integer Dim x As Double Dim x As String Dim x As Boolean Data Analysis using EXCEL

Private Sub CommandButton1_Click() Dim x As Integer x = 90 MsgBox ("x is " & x) End Sub Data Analysis using EXCEL

Swapping input values Private Sub CommandButton1_Click() Dim t As Integer t = Range("A2").Value Range("A2").Value = Range("B2").Value Range("B2").Value = t End Sub Data Analysis using EXCEL

InputBox Private Sub CommandButton1_Click() Dim A As Integer Dim B As Integer A = InputBox ("enter A") B = InputBox ("enter B") MsgBox ("A+B=" & A + B) End Sub Data Analysis using EXCEL

Worksheet object We can use worksheet Name or index Worksheets(1).Range(“A2”).Value Worksheets(‘sales’).Range(“A2”).Value Sheet1.Range(“A2”).Value Data Analysis using EXCEL

Data Analysis using EXCEL

If Then Statement Private Sub CommandButton1_Click() If Range("A2").Value = Range("B2").Value Then Range("C2").Value = "Equal" Else Range("C2").Value = " NotEqual " End If End Sub Data Analysis using EXCEL

LOGICAL OPERATIONS AND OR NOT Data Analysis using EXCEL

IF ELSE IF If exp then statement Elseif exp then statement End if Data Analysis using EXCEL

Case statement Select Case expression Case expressionlist1 statement1 statement2 .... .... .... Case Else elsestatement1 elsestatement2 .... .... End Select Data Analysis using EXCEL

Loops in VBA Data Analysis using EXCEL

For loop For variable = start To end [Step stepcount ] [statement 1] [statement 2] Next Data Analysis using EXCEL

Private Sub CommandButton1_Click() Dim i As Integer For i = 1 To 6 Cells( i , 1).Value = "welcome" Next i End Sub Data Analysis using EXCEL

Private Sub CommandButton1_Click() Dim i As Integer For i = 1 To 10 Step 2 MsgBox ( i ) Next i End Sub Data Analysis using EXCEL

DO WHILE Do While condition [statement 1] [statement 2] Loop Data Analysis using EXCEL

Dim i As Integer i = 1 Do While Cells( i , 1).Value <> "" Cells( i , 2).Value = Cells( i , 1).Value ^ 2 i = i + 1 Loop Data Analysis using EXCEL

DO UNTIL Do Until condition [statement 1] [statement 2] [statement n] Loop Data Analysis using EXCEL

i = 1 Do Until i > 5 'Condition is False.Hence loop will be executed i = i + 1 MsgBox ( i ) Loop Data Analysis using EXCEL

ACTIVITY Swapping range of Values Data Analysis using EXCEL

String Manipulation Join Left Right Len Mid Instr Data Analysis using EXCEL

Activity Separate strings Reverse Strings Data Analysis using EXCEL

Date and Time Current Date and Time : Now Hour, Minute, Second : Hour(Now) Data Analysis using EXCEL

Activity WeekDays Data Analysis using EXCEL

Array One-dimensional Array Two-dimensional Array Data Analysis using EXCEL

Activex Controls Command Button Text Box List Box Combo Box Check Box Data Analysis using EXCEL

Command Button Data Analysis using EXCEL

Text Box Data Analysis using EXCEL

List box Data Analysis using EXCEL

Combo box Data Analysis using EXCEL

Check Box Data Analysis using EXCEL

Option Button Data Analysis using EXCEL

stat Quantitative Data also known as continuous data, consists of numeric data that support arithmetic operations Natural scale Blood pressure, weight,amount spent Data Analysis using EXCEL

Qualitative Data A data sample is called qualitative , also known as categorical , if its values belong to a collection of known defined non-overlapping classes. Common examples include student letter grade (A, B, C, D or F), commercial bond rating (AAA, AAB, ... ) and consumer clothing shoe sizes (1, 2, 3, ... ). Data Analysis using EXCEL

Data Analysis using EXCEL

Excel and Advanced Excel with stat and Analysis

About This Presentation

Slide Content

Tags

Categories

Download

Quick Actions

Statistics

Related Slideshows

Excel and Advanced Excel with stat and Analysis

About This Presentation

Slide Content

Slide 1

Slide 2

Slide 3

Slide 4

Slide 5

Slide 6

Slide 7

Slide 8

Slide 9

Slide 10

Slide 11

Slide 12

Slide 13

Slide 14

Slide 15

Slide 16

Slide 17

Slide 18

Slide 19

Slide 20

Slide 21

Slide 22

Slide 23

Slide 24

Slide 25

Slide 26

Slide 27

Slide 28

Slide 29

Slide 30

Slide 31

Slide 32

Slide 33

Slide 34

Slide 35

Slide 36

Slide 37

Slide 38

Slide 39

Slide 40

Slide 41

Slide 42

Slide 43

Slide 44

Slide 45

Slide 46

Slide 47

Slide 48

Slide 49

Slide 50

Slide 51

Slide 52

Slide 53

Slide 54

Slide 55

Slide 56

Slide 57

Slide 58

Slide 59

Slide 60

Slide 61

Slide 62

Slide 63

Slide 64

Slide 65

Slide 66

Slide 67

Slide 68

Slide 69

Slide 70

Slide 71

Slide 72

Slide 73

Slide 74

Slide 75

Slide 76

Slide 77