Trimmean with two conditions

[msharma864512]

I want to calculate the average value for sector and quarter whrein it should exclude top5 and bottom 5 value from the respective data set.

For e.g, if we have to calculate the value in Cell I3(Avg for commodity chemicals in Q1 14), it should only calculate the average wherever quarter is Q1 14, and sub-sector is Commodity Chemicals, and it should also exclude top 5 and bottom 5 values from the respective data set while calculating average in any cell.

 

[Hui]

Here is a Table of the No of entries in each catgeory


What do you want to happen when there is less than 10 ?

 

[msharma864512]

Here is a Table of the No of entries in each catgeory
View attachment 49675

What do you want to happen when there is less than 10 ?

if number of entities is less than ten then it should take the actual avg only

 

[msharma864512]

it would also be helpful, if u could upload the spreadsheet too so that i can see the formula

 

[Hui]

there are only 3 entries with 10 or more records

 

[msharma864512]

there are only 3 entries with 10 or more records

Try the formula with excluding top 2 and bottom 2 values instead of 10, and wherever value is less than 2, it should just calculate the actual avg

 

[NARAYANK991]

Hi ,

See if this is OK.

I have not implemented the condition that if the number of entries is less than 2 , then the actual average should be taken.

I have excluded all the non-zero entries from the calculation.

Narayan

 

[p45cal]

Try the formula with excluding top 2 and bottom 2 values instead of 10, and wherever value is less than 2, it should just calculate the actual avg

This is silly because less than 2 can only be 1!
The smallest number of records where 2 can be taken off the top and two off the bottom leaving something to work with is 5.
The attached has this fun formula, array-entered into cell F3, then copied down and across:

=IFERROR(IF(SUMPRODUCT(($A$2:$A$5256=$E3)*($B$2:$B$5256=F$2)*ISNUMBER($C$2:$C$5256))>4,TRIMMEAN(IF(($A$2:$A$5256=$E3)*($B$2:$B$5256=F$2)*ISNUMBER($C$2:$C$5256)=1,$C$2:$C$5256),4/SUMPRODUCT(($A$2:$A$5256=$E3)*($B$2:$B$5256=F$2)*ISNUMBER($C$2:$C$5256))),AVERAGE(IF(($A$2:$A$5256=$E3)*($B$2:$B$5256=F$2)*ISNUMBER($C$2:$C$5256)=1,$C$2:$C$5256))),"-")

Array-entering means using Ctrl+Shift+Enter, not just Enter, to commit the formula to the sheet.
It will take two off the top and two off the bottom before averaging only if there are 5 or more records with numbers, otherwise it does a plain average.

 

[msharma864512]

Hi ,

See if this is OK.

I have not implemented the condition that if the number of entries is less than 2 , then the actual average should be taken.

I have excluded all the non-zero entries from the calculation.

Narayan

Thank You very much Narayan,

the formula was very helpful.