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

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
- #1: Start with AVERAGE
- #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.
*

### Go ahead and listen to the show

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Android | RSS

### Links & Resources mentioned in this session:

**Average Formula** – Syntax, examples

Special cases:

Statistics & Probability for analyst – a guide

### Transcript of this session:

Download this podcast transcript [PDF].

### 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.

**What about you? **What analytical techniques & formulas do you use apart from AVERAGE()? **Please share your thoughts using comments.**

### 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.

**For iPhone or iPod or iPad:****Click here to subscribe**.**Andriod Phones & Tabs:**Click here to subscribe on Stitcher. (You can download Stitcher free app from that link)**Windows Phone:**For Windows phone, search your podcasts app for our show.**Or…**As always, you can get latest episodes, show notes & resources from our Podcast page.

### Leave a Reply

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”

Have not listen to the podcast yet (waiitng to read the trancript instead) so maybe this is addressed already...

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?

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

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

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.

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

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

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

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.