0R72fZhU

1Inference of Excel
2
31. Inference for Moving Average
4The only difference between these two types of moving average is the sensitivity each one shows to changes in the data used in its calculation.
5More specifically, the exponential moving average (EMA) gives a higher weighting to recent prices than the simple moving average (SMA) does, while the SMA assigns equal weighting to all values. The two averages are similar because they are interpreted in the same manner and are both commonly used by technical traders to smooth out price fluctuations.
6The SMA is the most common type of average used by technical analysts and it is calculated by dividing the sum of a set of prices by the total number of prices found in the series. For example, a three-period moving average can be calculated by adding the following three sales together and then dividing the result by three (the result is also known as an arithmetic mean average).
7Since EMAs place a higher weighting on recent data than on older data, they are more reactive to the latest price changes than SMAs are, which makes the results from EMAs timelier and explains why the EMA is the preferred average among many traders.
82.Inference for Paired T Test – Excel
9Null Hypothesis Ho : There is no significant difference in time of exhaustion (minutes) between bicyclist takes Chocolate Milk and Carbohydrate Replacement
10Alternate Hypothesis H1 : There is a significant difference in time of exhaustion (minutes) between bicyclist takes Chocolate Milk and Carbohydrate Replacement
11α = 0.05
12Since P(T<=t) two-tail value(0.0822) is greater than 0.05, we accept the null Hypothesis i.e. 
13There is no significant difference in time of exhaustion (minutes) between bicyclist takes Chocolate Milk and Carbohydrate Replacement with t stat=1.986 and P=0.0822
143.Inference for Correlation Analysis – Excel
15Correlation coefficient (R) value ranges from -1 to +1
16< + 0.3 or -0.3 – No Relation (+ve or –ve)
170.3 to 0.5 or -0.3 to -0.5 – Weak Relation (+ve or –ve)
180.5 to 0.7 or -0.5 to -0.7 – Moderate Relation (+ve or –ve)
19> 0.7 or -0.7 – Strong Relation (+ve or –ve)
20In our dataset, Correlation coefficient value for High Temperature (®F) and Bottle water sales (cases) is 0.9324
21Since Correlation coefficient (R) value (0.9324) is greater than 0.7, we conclude that there is a strong positive relationship between High Temperature (®F) and Bottle water sales (cases)
224.Inference for Tables and Charts – Excel
23Table Elements
24	Header row
25	Banded rows
26	Calculated columns
27	Total row
28	Sizing handle
29
30Features of Excel Table – Managing Data
31	Sorting and filtering
32	Formatting table data
33	Inserting and deleting table rows and columns
34	Using a calculated column
35	Displaying and calculating table data totals
36	Using structured references
37	Ensuring data integrity
38Chart Types (Commonly Used)
39	Pie Chart
40	Column Chart
41	Line Chart
42	Bar Chart
43	Area Chart
44	Scatter Chart
455.Inference for Sorting and Filtering - Excel
46In the multiple column sorting that we have applied to column Genre and World Box Office Recipients,
47	In Action Genre, Avatar has highest World Box Office Recipient
48	In Animated Genre, Lion King has highest World Box Office Recipient
49	In Comedy Genre, Home alone has highest World Box Office Recipient
50	In Drama Genre, Titanic has highest World Box Office Recipient
51	In Sci-Fi Fantasy Genre, Marvel's The Avengers has highest World Box Office Recipient
52In the Filtering that we have applied to column Genre,
53	There are totally 9 movies in Animation Genre
54	In Animation Genre, only two movies (Lion King and Shrek2) have crossed 900 millions in US $
556.Inference for Conditional Formatting - Excel
56In the first case study Q1, Top 3 turnover items are
57	XY200
58	JJ120
59	AB101
60In the first case study Q2, Bottom 3 turnover items are
61	HA882
62	ZZ750
63	LL002
64In the second case study Q1, the custom formula is C11>15
65
66
67
687.Inference for Pivot Tables - Excel
69Case Study Q5:
70	Race
71Sex	Black	IndEsk	White	Grand Total
72Female	12	2	78	92
73Male	7	1	32	40
74Grand Total	19	3	110	132
75
76	There are totally 78 Females in Race White
77	There are totally 32 Males in Race White
78	There are totally 12 Females in Race Black
79	There are totally 7 Males in Race Black
80	There are totally 2 Females in Race IndEsk
81	There are only 1 Male in Race IndEsk
828.Inference for Lookup – Excel
83In First Case Study Q1,
84=VLOOKUP(I12,E8:F13,2)
85In Second Case Study Q1,
86=HLOOKUP(I24,F18:K19,2)
879.Inference for What-If analysis-Excel
88Using Goal Seek, we find out that 
89	To attain the total profit of $4700, 90% of the Books has to sold at higher price
90Using Scenario Manager and Data Table, we find out that
91	If we sell 70% of the books at higher price, then the total profit will be $4100
92	If we sell 80% of the books at higher price, then the total profit will be $4400
93	If we sell 90% of the books at higher price, then the total profit will be $4700
94	If we sell 100% of the books at higher price, then the total profit will be $5000
95For each 10% increase in selling books at higher price, then total profit increases by $300
9610.Inference for String Functions-Excel
97Case Study
98
99The no of characters in Los Angels is 10
100Formula: =LEN(E9)
101The position of string “Angels” in Los Angels Text is 5
102Formula: =SEARCH("Angels",E9,1)
103The uppercase of “Los Angels” text is “LOS ANGELS”
104Formula: =UPPER(E9)
105The lowercase of “Los Angels” text is “los angels”
106Formula: =LOWER(E9)
107
10811.Inference for Descriptive Statistics-Excel
109	The average height of the person is 163.5cm
110	The maximum height of the person is 186cm
111	The amount of deviations of height from the mean is 11.98cm
112	95% confidence interval: Height ranges from 156.8cm to 170.16cm (163.5±6.63)
113	The average weight of the person is 66.2Kg
114	The maximum weight of the person is 88kg
115	The amount of deviations of weight from the mean is 12.99kg
116	95% confidence interval: Weight ranges from 59kg to 73kg (66.2±7.19)
11712.Inference for Measure of variability -Excel
118	The average amount spend ($) by the customers is 66.51
119	The spending range ($) is 131.83
120	The amount of deviations from the average spending ($) is 29.28
121	Overall Customers spending is 44.02% varied across the average amount spending (66.51)
12213.Inference for Frequency Distribution -Excel
123In Quantitative variables, No of Bins to be selected ranges from 5 to 20. If the sample size is high, you have to choose maximum no of bins (20)
124Sometimes, No of Bins can also be calculated using the formula, No of Bins =  √(sample size)
125If sample size is 20,
126Bin size = √20 = 4.47≈ 5
127Class Interval is calculated using the formula max⁡〖- min〗/(No of Bins)
128A histogram is a plot that lets you discover, and show, the underlying frequency distribution (shape) of a set of continuous data. 
129The major difference is that a histogram is only used to plot the frequency of score occurrences in a continuous data set that has been divided into classes, called bins. Bar charts, on the other hand, can be used for a great deal of other types of variables including ordinal and nominal data sets.
13014.Inference for Regression - Excel
131	Dependent Variable (Y Range): No of Books read by the student(Y)
132	Independent Variables (X Range): No of Lectures they attend(X1), Grade(X2)
133	Multiple Regression Coefficient: 0.54 i.e. X1, X2 in group & Y are moderately positive related
134	R2 value is 0.297 i.e. 29.7% of variation in No of Books read by the student (Y) can be explained by No of Lectures they attend (X1) and Grade(X2) simultaneously
135	Our Model is not Fit, because R2 value has to be greater than 0.6 i.e. 60%
136	In ANOVA table, the significance F Value (0.0014) is less than 0.05. So, we can say that our test is statistically significant
137	General Linear Regression Equation for Prediction: Y=α + β1 X1 + β2 X2
138	α = Intercept Coefficient, β1 = Coefficient of X1 and β2 = Coefficient of X2
139	Our Regression Equation for Prediction is Y = -1.2433 + 0.09006 X1 + 0.03105 X2
140
14115.Inference for ANOVA – Excel
142Null Hypothesis Ho : There is no significant difference between people whether they taking Psychotherapy treatment, Family therapy treatment, Group  therapy treatment and , Behavioral therapy treatment
143Alternate Hypothesis H1 : There is a significant difference between people whether they taking Psychotherapy treatment, Family therapy treatment, Group  therapy treatment and , Behavioral therapy treatment
144α = 0.05
145Since P value in the ANOVA table (0.618494) is greater than 0.05, we accept the null Hypothesis i.e.  
146There is no significant difference between people whether they taking Psychotherapy treatment, Family therapy treatment, Group therapy treatment and, Behavioral therapy treatment with t F=0.60444 and P=0.6184
147
148
149
150Inference of SPSS
151
1521.	Inference for Correlation Analysis – SPSS
153Pearson Correlation coefficient (R) value ranges from -1 to +1
154< + 0.3 or -0.3 – No Relation (+ve or –ve)
1550.3 to 0.5 or -0.3 to -0.5 – Weak Relation (+ve or –ve)
1560.5 to 0.7 or -0.5 to -0.7 – Moderate Relation (+ve or –ve)
157> 0.7 or -0.7 – Strong Relation (+ve or –ve)
158In our first dataset, Correlation coefficient value for Height of Men and Length of Femur is 0.651 and the Sig. (2-tailed) value is 0.042
159Since, Correlation coefficient (R) value (0.651) is between 0.5 to 0.7 and Sig value < 0.05, we conclude that there is a significant moderate positive relationship between Height of Men and Length of Femur
160In our second dataset, Correlation coefficient value for Weight (1000lbs) and Fuel Efficiency (Miles/gallon) is -0.839 and the Sig. (2-tailed) value is 0.002
161Since, Correlation coefficient (R) value (-0.839) is greater than -0.7 and Sig value < 0.05, we conclude that there is a significant strong negative relationship between Weight (1000lbs) and Fuel Efficiency (Miles/gallon)
1622.	Inference for Linear Regression – SPSS
163•	Dependent Variable (Y Range): Overall Satisfaction(Y)
164•	Independent Variables (X Range): Price Satisfaction(X)
165•	Regression Coefficient: 0.585 i.e. X & Y are moderately positive related
166•	R2 value is 0.343 i.e. 34.3% of variation in Overall Satisfaction (Y) can be explained by Price Satisfaction (X)
167•	Our Model is not Fit, because R2 value has to be greater than 0.6 i.e. 60%
168•	In ANOVA table, the significance Value (0.000b) is less than 0.05. So, we can say that our test is statistically significant
169•	General Linear Regression Equation for Prediction: Y=α + β X
170•	α = Intercept Coefficient, β = Coefficient of X
171•	Our Regression Equation for Prediction is Y = 1.318 + 0.575 X
172
1733.	Inference for Multiple Regression – SPSS
174Model 2 Inference
175•	Dependent Variable (Y Range): Employee Code(Y)
176•	Independent Variables (X Range): Employee Category(X1), Beginning Salary(X2), Month since hire(X3), Previous experience(X4)
177•	Multiple Regression Coefficient: 0.998 i.e. X1, X2, X3, X4 in group & Y are strongly positive related
178•	R2 value is 0.997 i.e. 99.7% of variation in Employee Code (Y) can be explained by Employee Category(X1), Beginning Salary(X2), Month since hire(X3), Previous experience(X4) simultaneously
179•	Our Model is Fit, because R2 value has to be greater than 0.6 i.e. 60%
180•	In ANOVA table, the significance Value (0.0014) is less than 0.05. So, we can say that our test is statistically significant
181•	General Linear Regression Equation for Prediction: Y=α + β1 X1 + β2 X2+ …..
182•	α = Intercept Coefficient, β1 = Coefficient of X1 and β2 = Coefficient of X2
183•	Our Regression Equation for Prediction is Y = -69.90 + 0.369 X1 + 0.000 X2 -13.589 X3 + 0.002 X4
184
1854.	Inference for Cross tabulation – Chi Square
186Survey dataset output,
187Case Processing Summary
188	Cases
189	Valid	Missing	Total
190	N	Percent	N	Percent	N	Percent
191General happiness * Happiness of marriage	1330	47.0%	1502	53.0%	2832	100.0%
192General happiness * Happiness of marriage Crosstabulation
193Count
194	Happiness of marriage	Total
195	Very happy	Pretty happy	Not too happy	
196General happiness	Very happy	523	49	6	578
197	Pretty happy	310	357	14	681
198	Not too happy	19	35	17	71
199Total	852	441	37	1330
200Chi-Square Tests
201	Value	df	Asymp. Sig. (2-sided)
202Pearson Chi-Square	424.839a	4	.000
203Likelihood Ratio	390.298	4	.000
204Linear-by-Linear Association	312.491	1	.000
205N of Valid Cases	1330		
206a. 1 cells (11.1%) have expected count less than 5. The minimum expected count is 1.98.
207Symmetric Measures
208	Value	Asymp. Std. Errora	Approx. Tb	Approx. Sig.
209Ordinal by Ordinal	Gamma	.795	.025	20.628	.000
210N of Valid Cases	1330			
211
212X(General Happiness) – Ordinal Variable
213Y(Happiness of Marriage) – Ordinal Variable
214Case summary and count information gives you the no of valid cases and count falls in to each category
215Null Hypothesis Ho : There is no significant association b/w General Happiness and Happiness of Marriage
216Alternate Hypothesis H1 : There is a significant association b/w General Happiness and Happiness of Marriage
217In Chi-square table, Asymp Sig (2-tailed) value in Pearson chi-square is less than 0.05, so we can reject the null hypothesis and go for alternate hypothesis i.e. there is a significant association b/w General Happiness and Happiness of Marriage
218In symmetric measure table, Gamma value determines the strength of the relationship
219In our dataset, the Gamma value is 0.795 i.e. the association b/w general happiness and happiness of marriage is very strong
2205.	Inference for One-way Anova – SPSS
221Factor
222Employment Category (3 groups) - Nominal
223Clerical
224Custodial
225Manager
226
227Dependent List
228Current Salary - Scale
229ANOVA
230Current Salary
231	Sum of Squares	df	Mean Square	F	Sig.
232Between Groups	89438483925.943	2	44719241962.971	434.481	.000
233Within Groups	48478011510.397	471	102925714.459		
234Total	137916495436.340	473			
235Null Hypothesis Ho : There is no significant difference b/w average current salary based on Employment category
236Alternate Hypothesis H1 : There is a significant difference b/w average current salary based on Employment category
237In ANOVA table, Sig value is less than 0.05, so we can reject the null hypothesis and go for alternate hypothesis i.e. there is a significant difference b/w average current salary based on Employment category
2386.	Inference for two-way ANOVA – SPSS
239Factors
2401.	Employment Category (3 groups) - Nominal
241Clerical
242Custodial
243Manager
2442.	Gender (2 groups) – Nominal
245Male
246Female
247
248Dependent List
249Current Salary - Scale
250
251Tests of Between-Subjects Effects
252Dependent Variable: Current Salary
253Source	Type III Sum of Squares	df	Mean Square	F	Sig.
254Corrected Model	96456357285.104a	4	24114089321.276	272.780	.000
255Intercept	177271943071.927	1	177271943071.927	2005.313	.000
256jobcat	32316332041.298	2	16158166020.649	182.782	.000
257gender	5247440731.568	1	5247440731.568	59.359	.000
258jobcat * gender	1247682866.737	1	1247682866.737	14.114	.000
259Error	41460138151.236	469	88401147.444		
260Total	699467436925.000	474			
261Corrected Total	137916495436.340	473			
262a. R Squared = .699 (Adjusted R Squared = .697)
263
264
265
266Null Hypothesis Ho : 
267There is no significant difference b/w average current salary based on Employment category
268There is no significant difference b/w average current salary based on Gender
269There is no significant difference b/w average current salary based on Employment category and Gender
270
271Alternate Hypothesis H1 : 
272There is a significant difference b/w average current salary based on Employment category
273There is a significant difference b/w average current salary based on Gender
274There is a significant difference b/w average current salary based on Employment category and Gender
275
276In Tests of Between-Subjects Effects table, Sig value is less than 0.05 for all the three sources, so we can reject the null hypothesis and go for all the three alternate hypothesis 
2777.	Inference for Independent sample t test – SPSS
278A Levene's Test for Equality of Variances: This section has the test results for Levene's Test. From left to right:
279•	F is the test statistic of Levene's test
280•	Sig. is the p-value corresponding to this test statistic.
281
282Null Hypothesis Ho : There is homogeneity of variance exists b/w sales of vehicle type (Automobile and Truck)
283Alternate Hypothesis H1 : There is no homogeneity of variance exists b/w sales of vehicle type (Automobile and Truck)
284The p-value of Levene's test is printed as ".002" (but this should be read as p > 0.001 -- i.e., p very large), so we reject the null of Levene's test and conclude that the homogeneity of variance in sales variable not exists in terms of vehicle Type (Automobile or Truck). This tells us that we should look at the "Equal variances not assumed" row for the t-test (and corresponding confidence interval) results. (If this test result had been significant -- that is, if we had observed p > α -- then we would have used the "Equal variances assumed" output.)
285B t-test for Equality of Means provides the results for the actual Independent Samples t Test. From left to right:
286•	t is the computed test statistic
287•	df is the degrees of freedom
288•	Sig (2-tailed) is the p-value corresponding to the given test statistic and degrees of freedom
289•	Mean Difference is the difference between the sample means; it also corresponds to the numerator of the test statistic
290•	Std. Error Difference is the standard error; it also corresponds to the denominator of the test statistic
291
292Null Hypothesis Ho : There is no significant difference b/w average sales of vehicle type (Automobile and Truck)
293Alternate Hypothesis H1 : There is a significant difference b/w average sales of vehicle type (Automobile and Truck)
294Since Sig.(2-tailed( value in Equal variance not assumed of t-test result (0.024) is less than 0.05, we reject the null hypothesis and conclude that there is a significant difference b/w average sales of vehicle type (Automobile and Truck)
2958.	Inference for Descriptive statistics- SPSS
29645.6% in our dataset are female and 54.4% are male
297The mean of Current salary is $34,419.57
298The Range of Current salary is $119,250
299The Standard deviation of Current salary is $17,075.661
300The variance of Current Salary is 291578214.5
3019.	Inference for Tables and Charts – SPSS
302SPSS Custom Tables includes capabilities to help you:
303•	Get in-depth analyses so you can understand your data better and enhance reports for decision makers.
304•	Preview tables as you build them, ensuring that you create polished, accurate reports in less time.
305•	Customize table layout and appearance to communicate results clearly and accurately.
306•	Make results easily available by delivering information people can act on without further processing.
307Most commonly used charts in spss
3081.	Bar chart
3092.	Line chart
3103.	Area chart
3114.	Pie chart
3125.	Scatter chart
3136.	Histogram
3147.	Box-plot’
315
31610.	Inference for Sorting, Splitting and merging files in SPSS
317SORTING DATA
318Sorting data allows us to re-organize the data in ascending or descending order with respect to a specific variable. Some procedures in SPSS require that your data be sorted in a certain way before the procedure will execute. There are two options for sorting data:
3191.	Sort Cases (i.e., row sort)
3202.	Sort Variables (i.e., column sort)
321
322SPLIT FILE splits the active dataset into subgroups that can be analyzed separately. These subgroups are sets of adjacent cases in the file that have the same values for the specified split variables. Each value of each split variable is considered a break group, and cases within a break group must be grouped together in the active dataset.
323MERGING DATA
3241.	Add variables
3252.	Add cases
326
327
32811.	Inference for Data Transformation, Recoding and Select cases – SPSS
329Data Transformation
330Transforming data is performed for a whole host of different reasons, but one of the most common is to apply a transformation to data that is not normally distributed so that the new, transformed data is normally distributed. Transforming a non-normal distribution into a normal distribution is performed in a number of different ways depending on the original distribution of data, but a common technique is to take the log of the data.
331Recoding
332Sometimes you will want to recode a variable by grouping its categories or values together. For example, you may want to change a continuous variable into a categorical variable, or you may want to merge the categories of a nominal variable. In SPSS, this type of transform is called recoding.
333In SPSS, there are three basic options for recoding variables:
334•	Recode into Different Variables
335•	Recode into Same Variables
336•	DO IF syntax
337Select Cases Options: (similar to filter in Excel)
338•	All Cases (default initial setting)
339•	If condition is satisfied
340•	Random Sample of Cases
341•	Based on time or case range
342•	Use filter variable Selection
343
34412.	Inference for Introduction to SPSS
345SPSS Vs Excel
346When compared with Microsoft Excel, SPSS has:
347•	An easier and quicker access to basic functions, like descriptive statistics, in pull-down menus
348•	A wide range of charts and graphs to choose from
349•	Faster access to statistical tests
350
351Maximum No of Records in Excel: 1,048,576 rows by 16,384 columns
352SPSS 32 bit can hold up to 2 billion cases in a dataset.
353SPSS 64 bit has no real limitation except the specifications of your computer.
354
355SPSS Variable Labels and Value Labels are two of the great features of its ability to create a code book right in the data set.  Using these every time is good statistical practice.
356SPSS doesn’t limit variable names to 8 characters like it used to, but you still can’t use spaces, and it will make coding easier if you keep the variable names short.  You then use Variable Labels to give a nice, long description of each variable.