Calculating Average with multiple conditions

[msharma864512]

Hi

I want to calculate average using multiple conditions.

In the attached spreadsheet, I want to calculate average EBITDA margin for all sub-sectors by removing outliers (for e.g. top 5 and bottom 5 data of a that particular sector).

For e.g if we have to calculate the industry average in cell D2, then the industry average would be for the companies which are coming under Retail, and it should also exclude top 5 and bottom 5 EBITDA margin of companies coming under Retail.

Hence for all the companies coming under a particular sub-sector, their industry average should be same.

 

[bosco_yip]

Average EBITDA margin for sub-sectors, exclude top 5 and bottom 5.

1] In D2 array formula (confirm entered with SHIFT+CTRL+ENTER) copy down :

=IFERROR(TRIMMEAN(IF($B$2:$B$847=B2,$C$2:$C$847),10/COUNTIFS($B$2:$B$847,B2)),"No result, EBITDA Margin less than 10 nos")

2] See attached file

Regards
Bosco

 

[msharma864512]

Hi Bosco,

Thanks for the formula, but unfortunately this is not giving the correct number. And I dont understand how, 10/COUNTIFS($B$2:$B$847,B2) is excluding top 5 or bottom 5 numbers.

 

[NARAYANK991]

Hi ,

Can you say what should be the correct values for a few sectors ?

If you go through the Excel help on the TRIMMEAN function , it gives an example that may help.

Narayan

 

[msharma864512]

Hi,

For retail, it should be 7.3%, For Food it should be 14.2%, For IT services, it should be 15.6%.

I hope, it serves your query, and you get what I am trying to calculate here.

 

[NARAYANK991]

Hi,

For retail, it should be 7.3%, For Food it should be 14.2%, For IT services, it should be 15.6%.

I hope, it serves your query, and you get what I am trying to calculate here.

Hi ,

If you take the case of Retail , there are 64 records in this sector.

If you extract the data of the EBITDA margin for these 64 records , sort them in either ascending or descending order , delete the first 5 and the last 5 values , you are left with 54 values ; taking the average of these 54 values gives us 7.4395481% as the final result.

Can you explain how you get 7.3 % ?

Narayan

 

[msharma864512]

Hi ,

If you take the case of Retail , there are 64 records in this sector.

If you extract the data of the EBITDA margin for these 64 records , sort them in either ascending or descending order , delete the first 5 and the last 5 values , you are left with 54 values ; taking the average of these 54 values gives us 7.4395481% as the final result.

Can you explain how you get 7.3 % ?

Narayan

Thanks Narayan,

I got it now, your formula is correct, I was doing some mistake while calculating, thank you very much for your help.

But just one more thing, how did 10/COUNTIFS($B$2:$B$847,B2) helped in removing the top 5 and bottom 5 values

 

[NARAYANK991]

Hi ,

If you see the Excel help on the TRIMMEAN function , it says this :

The fractional number of data points to exclude from the calculation. For example, if percent = 0.2, 4 points are trimmed from a data set of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set.

To take their example :

Suppose the total number of data points = 20

Suppose we wish to exclude the top 2 and the bottom 2 points.

Total number of points to be excluded = 2 + 2 = 4

Therefore percentage of points to be excluded = 4/20 = 1/5 = 20%

To take your example :

The total number of data points is returned by the following :

COUNTIFS($B$2:$B$847,B2)

If B2 contains the text Retail , this will return the value 64.

Total number of points to be excluded = 10

Therefore percentage of points to be excluded = 10/64 , which is what has been entered in the formula as :

10/COUNTIFS($B$2:$B$847,B2)

Narayan

 

[msharma864512]

Hi ,

If you see the Excel help on the TRIMMEAN function , it says this :

The fractional number of data points to exclude from the calculation. For example, if percent = 0.2, 4 points are trimmed from a data set of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set.

To take their example :

Suppose the total number of data points = 20

Suppose we wish to exclude the top 2 and the bottom 2 points.

Total number of points to be excluded = 2 + 2 = 4

Therefore percentage of points to be excluded = 4/20 = 1/5 = 20%

To take your example :

The total number of data points is returned by the following :

COUNTIFS($B$2:$B$847,B2)

If B2 contains the text Retail , this will return the value 64.

Total number of points to be excluded = 10

Therefore percentage of points to be excluded = 10/64 , which is what has been entered in the formula as :

10/COUNTIFS($B$2:$B$847,B2)

Narayan

Thanks Narayan,

This information was very helpful

 

[msharma864512]

Hi Narayan,

In this case formula is written in such a way that its giving the text "No result, EBITDA Margin less than 10 nos", when there are less than 10 companies in a particular sector.

Is it possible, if there are less than companies in any case, it will take the median of those sectors.

 

[NARAYANK991]

Hi Narayan,

In this case formula is written in such a way that its giving the text "No result, EBITDA Margin less than 10 nos", when there are less than 10 companies in a particular sector.

Is it possible, if there are less than companies in any case, it will take the median of those sectors.

Hi ,

So when the number of companies in a sector is say 7 , what should be done ?

Narayan

 

[msharma864512]

in those cases, it should return the median of those seven companies

 

[NARAYANK991]

Hi ,

Use the following array formula , to be entered using CTRL SHIFT ENTER :

=IFERROR(TRIMMEAN(IF($B$2:$B$847=B2,$C$2:$C$847),10/COUNTIFS($B$2:$B$847,B2)),TRIMMEAN(IF($B$2:$B$847=B2,$C$2:$C$847),0))

Narayan