Measures of Central
Tendency in Excel: Mean,
Weighted Mean, Median,
Mode, Geometric Mean,
Harmonic Mean, and
Weighted Harmonic Mean
Measures of Central Tendency Overview
Measures of central tendency describe the center of a finite set of data or a theoretical distribution such as the normal distribution. Data is said to have a strong or weak central tendency based on measures of its dispersion such as standard deviation.
The following measures of central tendency can be calculated in Excel:
Mean (arithmetic mean) – the sum of all data values divided by the number of data values
Weighted mean – an arithmetic mean that incorporates weighting to certain data points
Median – the middle value that separates the upper half of the data set from the lower half of the data set
Mode – The most frequent value in the data set
Geometric mean – the nth root of the product of data, where there are n of these.
Harmonic mean – the reciprocal of the arithmetic mean of the reciprocals of the data values
Weighted harmonic mean
Mean (Arithmetic mean) in Excel
The mean is simply the sum of a collection of data values in a data set divided by the number of data values in the data set. The mean is very sensitive to outliers, i.e., very high or low values. If significant outliers exist the median can be a more robust measure of central tendency than the mean. Many parametric statistical tests rely on the calculation of the mean. Parametric tests are statistical tests which require that the data set be distributed according to a specified distribution such as the normal distribution. Many nonparametric tests rely on the calculation of the median. Nonparametric tests do not require that the data set be distributed according to a specified distribution.
A population mean is denoted by µ, the Greek letter mu.
A sample mean is denoted by x_bar or x_avg.
The formula to calculate the mean is as follows:
The Excel formula to calculate the mean is the following:
The weighted arithmetic mean is used if certain individual data values should be given greater weight due to increased importance or due to a larger number of occurrences of specific values. The formula for the weighted mean is as follows:
(Click On Image To See a Larger Version)
An instructor teaches two sections of the same course and gives the same test to both sections. The test average from section 1, which has 30 students, is 84. The test average of section 2, which has 20 students is 93. The average score of all students combined could be calculated using the weighted mean as follows:
Weighted Mean = (30*84 + 20*93) / (20 + 30) = 87.6
Median in Excel
Half of the values of a data set are larger than the mean and half of the values are smaller than the mean. If there is no single value, then the median is defined as being the average of the two middle values. There is no widely-accepted symbol for the median so any symbol used to denote the median must be explicitly defined. The median is the 2nd quartile, 5th decile, and 50th percentile. The median and mode are the only measures of central tendency that can be applied to ordinal data. Ordinal data are data that are ranked but whose values have no specific numerical meaning.
The Excel formula to calculate the median is the following:
Mode in Excel
The mode is the most frequently occurring value in a data set. A data set is said to be unimodal if there is only one data value that occurs significantly more often than the other data values. The PDF (probability density function) of a unimodal data set has a global maximum and no other local maxima. The bell-shaped normal distribution is unimodal. A data set is said to be multimodal if there are more than one value that occurs significantly more often than the other values in a data set. The PDF of a multimodal data set has at least two local maxima. If the PDF has only two local maxima, the data set is said to be bimodal. Three local maxima in the PDF designate the data set as being trimodal.
The mode is one of the few descriptors that can be applied to a data set that is nominal. Nominal data are merely labels that have no numerical significance or ranking.
The shape of a data set’s distribution can be observed by creating a histogram of the data.
The Excel formula to calculate the mode is the following:
MODE(data range) – This function is compatible with Excel 2007 and earlier
MODE.SING(data range) – This function can be used in Excel 2010 or later. It returns the most frequently occurring value in a data set or data range.
MODE.MULT(data range) – This function can be used in Excel 2010 or later. It returns a vertical array of the most frequently occurring values in a data set or data range. For a horizontal array, use the following:
Perhaps the quickest way to determine the modal values is to create an Excel histogram of the data. An example of this is shown as follows:
The histogram chart generated in Excel quickly shows that the data is bimodal and the two modes existing at 3 and 8.
Geometric Mean in Excel
The geometric mean is the nth root of the product of n numbers.
(Click On Image To See a Larger Version)
The Excel Formula for nth root is POWER(NUMBER,1/n). The geometric mean formula can be generalized in Excel as follows:
Geometric Mean =POWER(PRODUCT(data range),1/(COUNTA(data range))
COUNTA(data range) counts the number of cells in a range that are not empty.
A more direct way to calculate the geometric mean would be to use the following formula:
Geometric Mean = GEOMEAN(data range)
Combining Differently-Scaled Metrics into a Single Metric With Geometric Mean
The geometric mean provides a method to combine separate measures created on different scales into a single measure that is representative of all of the measures combined. The geometric mean is a way of normalizing the all measurements so that so that no one measure has a disproportionately large effect on the calculation of the geometric mean.
For example, products within a certain category might be rated for quality on a scale of 1 to 10 and also be rated for ease-of-use on a scale of 1 to 100. The geometric mean creates a single measure that combines both of these scores without allowing either of the scores to have a disproportionately large effect.
Brand A Quality Rating: 6
Brand A Ease-of-Use Rating: 70
Brand A Geometric mean = GEOMEAN(6,70) = 20.5
Brand B Quality Rating: 8
Brand B Ease-of-Use Rating: 25
Brand B Geometric mean = GEOMEAN(8,25) = 14.1
If the criteria of Quality Rating and Ease-of-Use Rating are considered equally important and are the two main criteria for selecting among brands within the category, Brand A would be the preferred brand as determined by its higher geometric mean of those two measures.
One very important characteristic of the geometric mean is the normalization of the scales of the different measures. The same percent change in either measure will produce the same change in the geometric mean.
Brand B Geometric mean = GEOMEAN(8,25) = 14.1
If Brand B’s Quality Rating is reduced by 50 percent from 8 to 4, the geometric mean now becomes the following:
Brand B Geometric mean = GEOMEAN(4,25) = 10
If Brand B’s Ease-of-Use Rating is reduced by 50 percent from 25 to 12.5, the geometric mean will also equal 10 as follows:
Brand B Geometric mean = GEOMEAN(8,12.5) = 10
Calculating Combined Growth Rate With Geometric Mean
Another important use of the geometric mean is to calculate a single “average” growth rate over periods of different growth rates that would produce the same overall amount of growth. For example, an investment of $1,000 would grow to $1,615 if it grew by 15 percent in the 1st year, 30 percent in the 2nd year, and 8 percent in the 3rd year. This can be calculated by the following:
1st year balance = $1,000 * (1.15) = $1,150
2nd year balance = $1,150 * (1.30) = $1,495
3rd year balance = $1,495 * (1.08) = $1,615
An average growth rate for all three periods would be calculated using the geometric mean as follows:
GEOMEAN(1.15,1.30,1.08) = 1.173154
The end balance after 3 years at this constant annual growth rate would be calculated as follows:
$1,000 * (1.173154)3 = $1,615
Harmonic Mean in Excel
The harmonic mean is often used to calculate an average of different rates or ratios. The harmonic mean is also used to calculate an average of a set of numbers that might have significant outliers. The harmonic mean would provide a truer representation of the average than the arithmetic mean because outliers have a disproportionate effect on the arithmetic mean.
The harmonic mean is calculated by the following formula:
(Click On Image To See a Larger Version)
The Excel formula to calculate the harmonic mean is the following:
Harmonic Mean = HARMEAN(data range)
Calculating the Average Rate Using the Harmonic Mean
One pump operates at 5 gallon/minute, a second pump operates at 7 gallons/minute, and a third pump operates at 9 gallons/minute. The average rate for all three pumps if each pump is used sequentially to pump the same amount as the other two pumps is calculated as follows:
HARMEAN(5,7,9) = 6.608392
If each pump was operated separately to pump out 40 gallons of water and they operated sequentially (one turned on immediately after another finished pumping 40 gallons of water), the average pumping volume over the entire time that it took all three pumps to collectively pump 120 gallons is 6.608392 gallons/min.
That can be verified as follows:
Total time = 120 gallons / (6.608392 gallons/minute) = 18.16 minutes
Total time = 40 gallons / (5 gal/min) + 40 gallons/(7 gal/min) + 40 gallons(9 gal/min) =
= 8 minutes + 5.7143 minutes + 4.4444 minutes = 18.16 minutes
Calculating the Average Speed Using the Harmonic Mean
If equal distances were individually travelled at different speeds, the harmonic mean would be used to calculate the average speed for the entire trip. For example Point A is 120 km from Point B. A train goes from Point A to Point B at 40 km/hour and then back to Point A at 60 km/hour, the average speed would be calculated using the harmonic mean as follows:
Average speed = HARMEAN(40,60) = 48 km/hour
This is verified as follows:
The total trip from A to B and then back to A would take the following amount of time travelling at 48 km/hour:
Total time = (120 km + 120 km)/(48 km/hour) = 5 hours
The total trip going from A to B at 40 km/hour and then from B to A at 60 km/hour would take the following amount of time:
Total time = (120 km)/(40 km/hour) + (120 km)/(60 km/hour) =3 hours + 2 hours = 5 hours
Calculating an Average when Significant Outliers Exist By Using the Harmonic Mean
One of the advantages that the harmonic mean has over the arithmetic mean is that outliers in the data set do not unduly skew the harmonic mean to the same degree that occurs with the arithmetic mean.
The following data set has a single significant outlier:
(3, 4, 6, 5, 7, 85, 6, 3, 5)
The harmonic mean of this data set is calculated in Excel as follows:
Harmonic mean = HARMEAN(3,4,6,5,7,85,6,3,5) = 4.988
The arithmetic mean of this data set is calculated in Excel as follows:
Arithmetic mean = AVERAGE(3,4,6,5,7,85,6,3,5) = 13.778
If the single outlier were removed, the harmonic and arithmetic means would be calculated as follows:
Harmonic mean = HARMEAN(3,4,6,5,7,6,3,5) = 4.462
Arithmetic mean = AVERAGE(3,4,6,5,7,6,3,5) = 4.875
The Harmonic Mean is the Preferred Method in Finance For Averaging Multiples
The harmonic mean gives equal weight to each data point and removes excessive influence that large outliers have on the arithmetic mean. Because of this quality, the harmonic mean is the preferred method in finance for averaging multiples such as the price/earnings ratio.
Calculating the Combined Resistance of Parallel Resistors Using the Harmonic Mean
The cumulative resistance of parallel resistor is calculated using the harmonic mean as follows:
Resistor 1 = 40Ω
Resistor 1 = 60Ω
Their combined resistance is place parallel to each other is the following:
Combined resistance = HARMEAN(40,60) = 48Ω
The harmonic mean is also in the same fashion to calculate the combined capacitance of capacitors arranged in series.
Weighted Harmonic Mean in Excel
The harmonic mean formula above assumes each rate has produced the same output, i.e., the same distance was travelled at each of the different rates or the same amount of liquid was pumped at each of the different rates. The different output occur at different rates, a weighted harmonic mean should be applied. The formula for this is as follows:
(Click On Image To See a Larger Version)
There is no easy way of automating this formula in Excel. The weighting and data values must be individually entered into the equation as in the following example:
A person travels in a train at 40 km/hour for 100 km and at 60 km/hour for 150 km. Calculate the average speed for the entire trip.
The weighting for the first part of the trip, w1, is set to 2. The weighting for the second part of the trip, w2, is set to 3. Any set of number could be used for w1 and w2 as long as w1 / w2 = 2/3.
The weighted harmonic mean would then be calculated as follows:
Weighted Harmonic Mean = (2 + 3) / (2/40 + 3/60) = 50
The average speed for the entire trip is 50 km/hour. This can be verified as follows:
The total distance travelled was 250 km. At 50 km/hour, the trip would take 5 hours.
Traveling at 40 km/hour for 100 km would take 2.5 hours (100km / 40 km/hour = 2.5 hours)
Traveling at 60 km/hour for 150 km would take 2.5 hours (150km / 60 km/hour = 2.5 hours)
The total trip time is 5 hours.
Excel Master Series Blog Directory
Click Here To See a List Of All
Statistical Topics And Articles In
You Will Become an Excel Statistical Master!
You mentioned in your blog about #PowerQualityaAnalysis is very nice to gain more knowledge about Harmonic Analysis. Harmonic distortions are one of the most common and irritating problems in industrial environment. We need to identify the source of harmonics and suppress them for a quality supply of power.We need to measure these harmonics at various points in the system and perform proper scientific analysis as per international standards.We need to measure these harmonics at various points in the system and perform proper scientific analysis as per international standards. If you have any queries about Harmonic Analysis, Please visitReplyDelete
Thanks for sharing your wealthy information. This is one of the excellent posts which I have seen. I go through your all of your blog, but this blog is the best one. It is really what I wanted to see hope in future you will continue for sharing such an excellent post.We appreciate that please keep going to write more content. We are the essay helper, we provide services all over the globe.Many times students are tensed for their projects and assignments, so at that time they can take online assignment help to complete it on time. There are experts available online who prepare the best essay writing service for you.ReplyDelete
Thanks for your post!
شركة شحن عفش من السعودية الى الاردن
شركة شحن عفش من جدة الى الامارات
شركة شحن عفش من جدة الى الاردن
IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. A IEEE Domain project Final Year Projects for CSE system development life cycle is essentially a phased project model that defines the organizational constraints of a large-scale systems project.ReplyDelete
IT Company Employess Productivity usually increases when a company implements corporate training courses on latest technologies.
corporate training in chennai
It Companies need of Corporate training programme arises due to improvement in technology, need for getting better performance or as part of professional development. corporate training companies in chennai Corporate Training refers to a system of professional development activities provided to educate employees.
corporate training companies in india
This is something i have never ever read, very detailed analysis.ReplyDelete
organic white rice
Đặt mua vé rẻ Aivivu, tham khảoReplyDelete
ve may bay tet gia re 2021
vé máy bay đi Mỹ Vietnam Airline
vé máy bay Việt Nam đi Pháp
vé máy bay đi du lịch hàn quốc
vé máy bay sang nhật bản
vé máy bay khứ hồi đi Anh
săn vé máy bay giá rẻ ở đâu
vé máy bay đi San Francisco giá rẻ
giá vé máy bay đi Los Angesles
Đặt vé má bay Aivivu, tham khảoReplyDelete
vé máy bay đi Mỹ hạng thương gia
vé máy bay quốc tế từ mỹ về việt nam
thời gian bay từ Việt nam sang Los Angeles
giá vé máy bay từ Toronto đến việt nam
Microsoft Excel offers several advanced maths features and formulas that can be found at www.Office.Com/Setup. Amazing explanation and great blog.ReplyDelete
Mathematics assignments are troubling me a lot, especially the statistics and quantitative analysis chapters. But this post has solved all of my problems has it has taught me the easiest way of doing calculations on excel. And for other courses, I must buy a custom assignment writing services online because I am unable to find the best help for them.ReplyDelete
คุณควรจำไว้ว่าข้อได้เปรียบของคาสิโนออนไลน์นั้นมีผลในระยะยาว https://casinodafabet.com/ หมายความว่าในระยะสั้นและด้วยโชคและโชคลาภเล็กน้อย ไม่มีเหตุผลใดที่คุณไม่ควรพบว่าตัวเองชนะในคาสิโนออนไลน์ReplyDelete
นี่คือเหตุผลที่คุณควรเล่นเกมดีลเลอร์สดในระหว่างเดินทาง https://k9winplay.org/ เฉพาะเมื่อคุณแน่ใจว่าจะมีการเชื่อมต่ออินเทอร์เน็ตที่เสถียรตลอดช่วงการเดิมพันทั้งหมดReplyDelete
The contents are masterpiece. you’ve performed a wonderful job in this topic! 메이저사이트ReplyDelete
Thanks for sharing excellent informations. Your web site is very cool. I am impressed by the details that you’ve on this site. 야설ReplyDelete
Feel free to visit my blog :
Admiring the time and energy you put into your blog and in depth information you offer. It's good to come across a blog every once in a while that isn't the same outdated rehashed material. Fantastic read! I've bookmarked your site 일본야동ReplyDelete
Feel free to visit my blog : 일본야동
There is definately a great deal to learn about this subject. I really like all of the points you have made.|ReplyDelete
I wan’t going to comment as this posts a bit old now, but just wanted to say thanks. 바카라사이트ReplyDelete
That's a really impressive new idea! casino api It touched me a lot. I would love to hear your opinion on my site. 카지노사이트ReplyDelete
Wonderful items from you, man. I’ve have in mind your stuff previous to and you’re just extremely excellent 스포츠토토ReplyDelete
Looking at this article, I miss the time when I didn't wear a mask. 오공슬롯 Hopefully this corona will end soon. My blog is a blog that mainly posts pictures of daily life before Corona and landscapes at that time. If you want to remember that time again, please visit us.ReplyDelete
This was an extremely nice post. Taking a few minutes and actual effort to generate a top notch article.ReplyDelete
This is the perfect post. It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.ReplyDelete
This is the post I was looking for 메이저사이트 I am very happy to finally read about the Thank you very much. Your post was of great help to me. If you are interested in the column I wrote, please visit my site .ReplyDelete
What a post I've been looking for! I'm very happy to finally read this post. 토토사이트 Thank you very much. Can I refer to your post on my website? Your post touched me a lot and helped me a lot. If you have any questions, please visit my site and read what kind of posts I am posting. I am sure it will be interesting.ReplyDelete
Student are commonly using Law coursework writing service to get cheap and authentic Law assignments. If you are bothered with your college essay, you could approach us without any hesitation.ReplyDelete
Succeed! It could be one of the most useful blogs we have ever come across on the subject. Excellent info! I’m also an expert in this topic so I can understand your effort very well. Thanks for the huge help. 안전놀이터ReplyDelete
I'm having a lot of trouble with my math homework, especially the statistics and quantitative analysis chapters. However, this post has answered all of my concerns by teaching me the simplest way to perform computations in Excel.or if you are Looking for the best leather jacket in the USA, then you should direct yourself towards celebs in leather jackets for all kinds of outfits at a cheap price.ReplyDelete
I just read your essay and found it to be very informative and valuable. I appreciate your efforts on this. If you're looking for wordpress website development , we can help.ReplyDelete
Howdy! This is kind of off topic but I need some advice from an established blog. Is it very difficult to set up your own blog? I’m not very techincal but I can figure things out pretty fast. I’m thinking about setting up my own but I’m not sure where to begin. Do you have any ideas or suggestions? Appreciate it . nice info thanks This is nice and awesome thanks for share us.Fantastic Blog…I will definitly share your blog with other people.Thank . I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. 스포츠토토ReplyDelete
This was really a big help for my research. I really appreciate your website. Thanks Again. 토토사이트ReplyDelete
Wow! Such an amazing 먹튀검증사이트ReplyDelete
it might not often be a taboo subject but typically everyone is inadequate to communicate on such topics. 카지노톡ReplyDelete
It’s a great and helpful piece of information. I am glad that you simply shared this information with us. I also refer to our article about these sources are trustworthy to use as tools for research. thank you so much for sharing this, it is extremely useful. Thanks again . Thanks for providing such good training. & Thank you for giving us such a great information. Thank you for sharing the information. Looking for such more content in the future. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it. 토토SOSReplyDelete
my loved one! I wish to 토토사이트주소ReplyDelete
i stumbled upon this i’ve discovered it in reality useful and it has helped me out loads. 해외메이저놀이터ReplyDelete
what i do not understood is in 007카지노주소ReplyDelete
Thanks for the meal!! But yeah, thanx for spending time to talk about this topic right here for your site. 파워볼놀이터ReplyDelete
hey there, i do think your website online is probably having browser compatibility issues. 놀이터토토ReplyDelete
hi, i do assume this is a high-quality blog. I stumbledupon it 😉 i might also return once more since i have book-marked it. Money and freedom is the best manner to trade, may additionally you be rich and retain to guide other humans. You are so top notch! I do not suppose i've examine a unmarried ele be useful to examine articles from different writers and use something from their websites. 우리카지노가입ReplyDelete
Simple however very accurate data thanks for sharing this one. A should study submit! 에볼루션카지노쿠폰ReplyDelete
i surely desirainly to my friends. With thank you; from anyone. 꽁머니지급ReplyDelete
Thank you!! Became searching on aol for some thing else, though i am right here now and would just like to EOS파워볼사이트ReplyDelete
Objectual systems limited is where ambition meets innovation, honesty makes collaboration and not viable to teamworkReplyDelete
그리고 대니는 테리에게 카지노 금고를 털었다는 전화 한 통을 다파벳 건다. 테리는 일을 수습하기 위해서 카지노 보안실로 향한다. 그러나 대니와 동료들은 이미 라스베이거스를 빠져나갔다. 오늘은 상봉카지노펍 상봉역홀덤 펍인 슈에뜨펍에 다녀왔어요! 라고 생각했는데 제 생각과는 달리...손님들이 가득 찰 정도이니 만약 입구가 외부에 있었다면 건물을 통으로 써야 했을 거예요!????????ReplyDelete
Hi, I also visit this site daily; this webpage is truly pleasant & users are openly sharing fastidious thoughts LOCKSMITH YORKReplyDelete
이것은 정말 좋은 사이트 게시물입니다. 메이저사이트ReplyDelete
You have done an amazing job! 에볼루션 카지노ReplyDelete
이 블로그가 정말 저를 설득했다고 말하고 싶습니다! 스포츠토토ReplyDelete
처음으로 여기를 방문하고 블로그에서 흥미로운 내용을 많이 발견했습니다. 특히 토론입니다. 감사합니다. 레깅스룸ReplyDelete
이 주제에 대한 정보를 한동안 찾고있었습니다. 707벳주소ReplyDelete