# CP010: Averages are Mean – 8 Techniques for making your analysis above average

Posted on May 30th, 2014 in Chandoo.org Podcast Sessions , Learn Excel - 8 comments

In the 10th session of Chandoo.org podcast, lets make your analysis above AVERAGE.

### What is in this session?

This is a continuation of Session 9 – Averages are mean

In the earlier episode, we talked about AVERAGE and why it should be avoided. In this session, learn about 8 power analysis techniques that will lift your work above averages.

In this podcast, you will learn,

• Re-cap – Why avoid averages
• 8 Techniques for better analysis
• #2: Moving Averages
• #3: Weighted Averages
• #4: Visualize the data
• Conclusions

NOTE: This is a 2 part podcast. Listen to first part before hearing this.

### Links & Resources mentioned in this session:

Average FormulaSyntax, examples

Moving Average in Excel

Weighted Average in Excel

Special cases:

Statistics & Probability for analyst – a guide

### How do you raise above average?

For many of my reports I start with AVERAGE and then improve the metrics to show insights. I try weighted average, median, mode, visualizations and conditional averages.

### Subscribe to Chandoo.org Podcast

Do you know that you can subscribe and receive latest episodes of our podcast right to your ears? Use one of the below links to get started.

 I struggle doing __________ with Excel. Fill in the blanks…, Matching transactions using formulas [Accounting]

### 8 Responses to “CP010: Averages are Mean – 8 Techniques for making your analysis above average”

1. Esteban says:

but what I always find is problematic when trending/averaging or sumarizing data in any other form is that data some times has "brake points"... points where behaviour changes (you can clearly see tehm in a chart)... trending, averaging or conoslidating in any way accorss this "break bounaries" is usually a problem and generates misleading indicators....
So I always think I need to find an automated and easy way to identify this breaks and then do my average or trend only within the valid group of data points... I still have not found any good formulas or method.. any thoughts?

2. Rajib says:

Hi Chandoo / Hui / Other Experts,

I have a query regarding using non-array formula on arrays.

e.g. I have 4 columns with value like these:

A1 Abhik
A2 Abheek
A3 Avik
A4 Aveek

I want to find the longest word in this array using sumproduct.

Sumproduct(len(A1:A4)) = 20
Alt+TUF shows
Sumproduct({5;6;4;5}) >> 20
When I apply Sumproduct(large(len(A1:A4),1); Alt+TUF shows
Sumproduct(large(len("Abhik"),1)) = 5; but this is not the desired outcome.
However, when I pass the same array separately in sumproduct(large({5;6;4;5}),1) it shows 6 which is correct result.

I'm not able to understand why Sumproduct is behaving separately with Large(Len()) combinations, when it works fine with Large, Len or the array {5;6;4;5} separately?

I hope I was clear with the question. If not please let me know.

Regards

• Rajib says:

And sorry for being off-topic, but could not figure out alternative way to approach 🙁

• Rajib says:

While I was trying out to make it work. I realized that sumproduct can create an array only from the first function. When multiple functions are nested, it does not work any more as an array & you have no other way but to press CSE array.

e.g. Sumproduct(round("Range",0)) generates an integer & give right answer. However, if we modify Sumproduct(large(round("Range",0),{1;2}) the array property does not work any more!

Sorry I'm only spamming the comments section. But as I'm exploring, sharing the observations. So that I get valuable inputs from all you experts.

Even in your last post, many people have asked array as an issue. May be you can dedicate a couple of posts on that!

Regards.

• NARAYAN says:

Hi Rajib ,

SUMPRODUCT on its own always operates on arrays ; once there are functions nested within the SUMPRODUCT function , the overall behaviour of the formula depends on whether the nested functions also by default operate on arrays , or they operate on arrays only if they are array entered using CTRL SHIFT ENTER.

To take a simple example , suppose you have a list of numbers from 1 through 9 , with repetitions in a random order ; let us name this Numbers.

To find out the sum of all those numbers in this list which are greater than 3 , we can use the formula :

=SUMPRODUCT((Numbers>3)*Numbers)

This will return the correct result without the need for CTRL SHIFT ENTER , since there is no nested function , and SUMPRODUCT , by default , operates on arrays.

We can get the same result using a somewhat roundabout way , by :

=SUMPRODUCT(--(IF(Numbers>3,Numbers)))

But this formula will return the correct result only if it is array entered , using CTRL SHIFT ENTER ; the nested function IF does not operate on arrays by default ; however , if it is array-entered , it will operate on arrays.

The LARGE and SMALL functions also work on arrays , but the point is that invariably , we use these functions along with some criteria , which are enforced using the IF function , and the latter is not an array function by default.

For example , suppose our list of numbers had repetitions ; suppose we want to know the sum of all the repeated smallest numbers , whatever it may be. We can use :

=SUMPRODUCT(Numbers*(Numbers=SMALL(Numbers,1)))

This does not have to be array-entered.

However , the moment you write this functionality using an IF statement , as in :

=SUMPRODUCT(--IF(Numbers=SMALL(Numbers,1),Numbers))

the formula will return the correct result only if it is array-entered.

Narayan

• Rajib says:

Thanks Narayan. I understand.

Is there a way, from microsoft formula help or somewhere, we can see the list of all the formulas that use array as input or output?

e.g. Sumproduct, Aggregate, Large, Small, MMult etc.

Regards
Rajib

• NARAYAN says:

Hi Rajib ,

Sorry , but I have no knowledge.

The confusing part is that there are several functions such as MATCH , COUNTIF , which are shown in the official help documentation as having a syntax where one of the parameters is a single-cell reference ; but over the years , experts have seen that these functions can accept multi-celled ranges in such parameters also.

For example MATCH accepts an array as its first parameter , as in :

=MATCH(List,List,0)

where List is a row / column vector.

Similarly , COUNTIF accepts an array in its second parameter , as in :

=COUNTIF(List,List)

In such cases , these will have to be array entered , using CTRL SHIFT ENTER.

Narayan

3. Joyce Swensson says:

Thank you for everything you do for the analyst community. The ability to read the podcast transcript is a really appreciated. BTW, I enjoyed hearing your children in an earlier podcast.

 I struggle doing __________ with Excel. Fill in the blanks…, Matching transactions using formulas [Accounting]