Understanding ANOVA Techniques with Excel and Learning to Decipher Variances

BlogDate_4/14/21

Topic: Analysis of Variance (ANOVA)

                                                            As I progress through the assignments in this class, I have begun to feel a deep sense of satisfaction and empowerment from the knowledge gained and the skillsets obtained. Up until I began taking this class, there were certain aspects of mathematics (statistics in particular) and relative applications in my professional life with which I was quite familiar but had always felt intimidated by because I was not leveraging the statistical concept along with the application to their full potential. Case in point, Microsoft Excel. I have leveraged the application daily to quantify variances and similarities in exceptionally large data sets for many years, but while I am sufficiently comfortable reading, analyzing, and interpreting, I have always felt frustrated by an inability to manipulate data sets within Excel myself., particularly with regards to Analysis of Variance (ANOVA). This is a tool I could often leverage in my professional life but lack the know-how. Additionally, while I was aware of the immense capabilities the application possessed, I did not have sufficient training and practice to leverage these capabilities in my work/home life. However, that completely changed for me after completing the Excel Data Analysis Tool Pak assignment. Looking over the assignment at first, I was apprehensive, but after installing the Tool Pak and running through the Laundry list of analysis tasks, I was elated; not only with being able to perform all the ANOVA functions within the data tool set, but with the understanding of how each functioned operated and WHY it was important to the data in question. For example, I never really understood the difference between a t-test, which observes the sample mean and ANOVA, which examines the variance (FordHamStats, 2010). Additionally, I could not explain the significance of each ANOVA function and its interpretation of the data at hand. I think the most valuable capability Excel retains is the ability to produce both Descriptive and Inferential statistical data. Armed with my newly acquired knowledge and confidence, I considered satellites (a subject of keen interest to me) and the host of data accompanying them. After some research, I was able to download a data sheet of all satellites by country via the Union of Concerned Scientists (UCS) webpage. The file was quite large, as there was a host of data available, from owner/operator, to apogee, longitude, perigee, inclination, class of orbit, orbit type, etc. I must confess, I have never been so excited about observing and manipulating data, but it was meaningful. Consider that there are well over 3,000 satellites in 4 different orbital classes spotted about the globe right now (UCS, 2021). What are they doing? What is the function of each? Which countries have the most? Is the number of satellites per country skewed, or proportional? The questions were endless for me. The real difference-maker was the knowledge and ability to perform the functions which could answer these questions. So, with that, I began experimenting with different functions, taking my time to understand what the variability between the different factors indicated. The spreadsheet proved to be an enjoyable wormhole of data, and I have included it with this post. In retrospect, the concepts, and skills I have acquired in the past couple weeks relate to the job duties I perform daily, and that is exciting for me. With regards to the text material, I think 9.6.1, Simple Linear Regression Analysis tied in with the ANOVA concepts I learned rather nicely because the scatter plots offer such a quick visual medium for assessing variability within large data sets like the UCS Satellite data (Khisty et al., 2012). As I continue forward in this class, I am just trying to focus on one module at a time, absorbing quality data and pondering its significance in my life.

 

 

 

 

Perigee (km)	3372	24543339	7278.570285	178534742.6
Apogee (km)	3372	27894247	8272.315243	304840208.3
Variance of Satellite by Type of Orbit, Longitude, Apogee, Perigee,
ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Rows	1.42838E+12	3371	423725880.4	7.103645997	0	1.058305
Columns	1664973966	1	1664973966	27.91282334	1.3496E-07	3.844219
Error	2.01077E+11	3371	59649070.44
Total	1.63112E+12	6743

(UCS, 2021)

Anova: Single Factor
SUMMARY
Groups	Count	Sum	Average	Variance
# Launched	34	3372	99.17647059	47164.27094
USA	34	1903	55.97058824	27950.57487
China	20	405	20.25	541.7763158
Russia	34	174	5.117647059	43.98573975
United Kingdom	20	166	8.3	530.6421053
France	34	13	0.382352941	0.667557932
Spain	14	21	1.5	1.653846154
Germany	34	40	1.176470588	4.331550802
Japan	34	89	2.617647059	14.84937611
Italy	34	13	0.382352941	0.546345811
Argentina	34	28	0.823529412	6.08912656
Israel	34	16	0.470588235	0.983957219
ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Between Groups	343596.6676	11	31236.0607	4.34537363	4.19E-06	1.816209
Within Groups	2501545.332	348	7188.348656
Total	2845142	359
(UCS, 2021)

 

Anova: Two-Factor With Replication
SUMMARY	179	215	1634	Total
2
Count	11	11	11	33
Sum	491	393	3028	3912
Average	44.6364	35.72727	275.2727	118.5455
Variance	9693.65	8061.618	472623.8	165922.6
Total
Count	11	11	11
Sum	491	393	3028
Average	44.6364	35.72727	275.2727
Variance	9693.65	8061.618	472623.8
(UCS, 2021)
ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Sample	0	0	65535	65535	#NUM!	#NUM!
Columns	405733	2	202866.6	1.24108	0.303491	3.31583
Interaction	0	0	65535	65535	#NUM!	#NUM!
Within	4903791	30	163459.7
Total	5309524	32
(UCS, 2021)

 

 

 

 

 

 

 

 

 

 

 

Anova: Two-Factor Without Replication
		Descriptive Data
SUMMARY	Count	Sum	Average	Variance
USA	4	2058	514.5	563427
China	4	405	101.25	19525.58
Russia	4	174	43.5	1619.667
UK	4	293	73.25	3852.25
France	4	20	5	22
Spain	4	21	5.25	36.91667
Germany	4	40	10	349.3333
Italy	4	13	3.25	27.58333
Japan	4	82	20.5	620.3333
Argentina	4	28	7	161.3333
Israel	4	16	4	38
Total	4	3150	787.5	1063326
Elliptical	12	360	30	3528.909
GEO	12	670	55.83333	10316.88
MEO	12	608	50.66667	10006.97
LEO	12	4662	388.5	583503
(UCS, 2021)
ANOVA			Inferential Data
Source of Variation	SS	df	MS	F	P-value	F crit
Rows	2785222	11	253202	2.144848	0.04469	2.093254
Columns	1063326	3	354441.9	3.002441	0.04437	2.891564
Error	3895691	33	118051.3
Total	7744239	47
(UCS, 2021)

 

 

 

 

 

FordhamStats. (2010, May 7). Excel Techniques - 11 - ANOVA - Two Factor without Replication [Video]. YouTube. https://www.youtube.com/watch?v=STqxo4ToN18

Khisty, C. J., Mohammadi, J., & Amedkudzi, A. A. (2012). Systems engineering with economics, probability, and statistics (2nd ed.). J. Ross Publishing.

Association, 5(10). https://doi.org/10.1161/jaha.116.004142

Union of Concerned Scientists (UCS). (2021, January 1). UCS Satellite Database. UCSUSA.org. https://www.ucsusa.org/resources/satellite-database