All articles with 'average()' Tag
“I was trying to calculate the average DJIA # by month. I had the data by day so tried various combos (none of which worked) (:-”
Learn how to help Suzanne average the DJIA on a particular day and learn about a new Excel Function all in one post.Continue »
Today I am asking you a tricky formula question. This is asked by Ionel on the Introduction to VLOOKUP, OFFSET & MATCH Formulas post. The question is, I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help [...]Continue »
Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.
What is weighted average?
Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”
Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.Continue »
The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard. Now, if she wants average of all values, we can use AVERAGE() formula if she wants top 5 values [...]Continue »
As part of our spreadcheats, today we will learn how to calculate moving average using excel formulas. As a bonus, you will also learn how to calculate moving average of latest 3 months alone using the count, average and offset formulas. Once you finish the tutorial, you have some home work to do as well. So go ahead and learn how to calculate moving averages.Continue »
Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing the error.Continue »