• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate average omitting min 5% & max % values in range

A

ajinkya deshmukh

New Member
Dear All,

I need some help with calculating average for data wherein I need to exclude lowest 5 % values & highest 5 % values (Column B in sheet "Avg") from a data range for each product across countries.

Also need to calculate average for only lowest 5 % values (column C in sheet "Avg".

I have attached the file with raw data wherein sheet "Data" is raw data to be used & sheet "Avg" is wherein I need the average as per the products & country.

Appreciate your help on this.

Thanks
Ajinkya Deshmukh
 

Attachments

  • Avg.xlsx
    96.8 KB · Views: 9
Hi Ajinkya,

See the attached file. Is this what you require?
I had turned your raw data into Excel Table.

Somendra.
 

Attachments

  • Avg.xlsx
    104.1 KB · Views: 7
Dear Somendra

Thank you for your help.

But this not what I wanted. The formula which you have provided does not exclude the lowest 5 % & highest 5 % values. The average comes total of all the data for each product.

Also when you select different country from drop down, it does not show any value for USA.

For Column C, it does not exclude lowest 5 % values. It should refer Column D in sheet "Data".

Regards
Ajinkya
 
@ajinkya deshmukh

Let me understand first here, you want to average of column C from Data sheet
"Average excluding highest 5 % & lowest 5 % values" for product say "C11" in "Canada". But the same product can also have value in column D as well so value in column C where you also have value in column D that should not be consider in taking average.

Similarly for the next case also.
Somendra.
 
Hi ajinkya,

I suggest you try AVERAGEIFS() if you are using XL2010 or show a manual example of what you need.
 
Dear Somendra

Yes you are right regarding the average excluding highest 5 % & lowest 5 % values.

In Avg Sheet, in column B, I want average for Column C (Data Sheet) excluding highest 5 % & lowest 5 % values for product say "C11" in "Canada". You had provided the values without excluding n without referring to country.


Step 2 - In Avg Sheet, in column C, I want average for Column D (Data Sheet) only for lowest 5 % values for product say "C11" in "Canada".

Let me know if this is clear to understand.

Ajinkya
 
Hi Ajinkya ,

Can you explain in a little more detail ?

To take an example , your first product is C11 , and the country is Canada ; the lowest value is 609.43 , and the highest value is 3190.

What is the meaning of lowest 5 % and highest 5 % ?

Narayan
 
Hi Narayan

For product C11 & Country Canada, the average formula should exclude lowest 5% & highest 5% of the total values for product C11

Reagrds
Ajinkya
 
Hi Ajinkya,

Still not clear because your data sheet column C has title Average excluding highest 5% and lowest 5%.

Can you please give an example sheet with say 10 nos. of data, what you want?

Somendra.
 
Oops, you need to ignore the title mentioned in Column C. I had mentioned it similar so that the reference can be easier but I believe it was confusing.

I am attaching the file with changes wherein in the Sheet "Avg" I have calculated manually the average for Products for Canada or USA individually. There is another sheet "Data used for Showing example" which has the data in which I have removed the highest 5 % & lowest 5 % values in column C. In column D I have kept values only for those which are for lowest 5% values.

The sheet "Data" will have actual data used for showing the example.

Ajinkya
 

Attachments

  • Avg_v1.0.xlsx
    47 KB · Views: 7
Hi Ajinkya,

Please find the attached file.
I had taken the reference of your example data to match your manual value and see if the formula is working right. You can change the references in the formula. I had used AVERAEIFS() formula.

If you face any problem in changing the ref. do let me know.

Somendra.
 

Attachments

  • Avg_v1.0.xlsx
    49.2 KB · Views: 3
Hi Ajinkya ,

Can you check the uploaded file ? I have used several helper columns so that we can verify the correctness of the logic. My results and yours are differing in some areas ; can you cross-check and give feedback ?

Narayan
 

Attachments

  • Avg_v2.0.xlsx
    52.3 KB · Views: 7
Somendra

The data which has to be used is from Sheet "Data" & not from "Data used for Showing example".

From sheet "Data used for Showing example", I have already removed value for highest 5% & lowest 5 %.

The sheet "Data" is without excluding highest 5% & lowest 5 % on which the formulas are to be applied to get the reference values what I had shown.

If you exclude highest 5% & lowest 5 % value then it should match with the reference value which I had provided.

Do let me know if you still face any issues.

Ajinkya
 
@ajinkya deshmukh

Sorry I am still under confusion. Kindly clear what will be the result if below is the case.

Product: C11
Country: Canada
Data Sheet Column C value: 1.1 ; 2.2 ; 3.3 ; 2.8 ; 4.5 ; 6 ; 7 ; 8 ; 9 ; 10.3 ; 11.1
Data Sheet Column D Value: 0 , 0 , 0 , 0 , 0 , 2.3 , 3.4 , 0 , 0 , 4.5 , 7.8 , 0 , 0

What will be the result from this and what you will include and what you will exclude?

Somendra.
 
What about
=AVERAGEIFS(Data!C2:C803,Data!B2:B803,$B$3,Data!A2:A803,A7,Data!C2:C803,">"&PERCENTILE(Data!C2:C803,0.05),Data!C2:C803,"<"&PERCENTILE(Data!C2:C803,0.95))

I'd add a few Named Formula to simplify the formulas as well
Resulting in
=AVERAGEIFS(Data,Organisation,$B$3,Product,A7,Data,">"&PERCENTILE(Data,0.05),Data,"<"&PERCENTILE(Data,0.95))
 

Attachments

  • Avg_v1.0.xlsx
    48.8 KB · Views: 6
NARAYANK991 said:
Hi Ajinkya ,

Can you check the uploaded file ? I have used several helper columns so that we can verify the correctness of the logic. My results and yours are differing in some areas ; can you cross-check and give feedback ?

Narayan
Click to expand...

Dear Narayan

Thanks a lot for your help.

I did few modifications to the formula & was able to match it as per the required result.

Ajinkya
 
Hui said:
What about
=AVERAGEIFS(Data!C2:C803,Data!B2:B803,$B$3,Data!A2:A803,A7,Data!C2:C803,">"&PERCENTILE(Data!C2:C803,0.05),Data!C2:C803,"<"&PERCENTILE(Data!C2:C803,0.95))

I'd add a few Named Formula to simplify the formulas as well
Resulting in
=AVERAGEIFS(Data,Organisation,$B$3,Product,A7,Data,">"&PERCENTILE(Data,0.05),Data,"<"&PERCENTILE(Data,0.95))
Click to expand...


Hui,

Thank you for the updated file.

I will check on the formula provided by you today

Ajinkya
 
Hello,
I have not checked all of the solutions above, but I interpreted the original post as follows:
  1. Calculate the average of the DataColC that matches a specific product and organization, excluding the values that are in the bottom and top 5%.
  2. The determination of bottom and top 5% should only count those rows where the product and country matches the input values

in cell B7, enter the following array formula to obtain the average for those values that are 5% to 95%:
=AVERAGE(IFERROR(IF((LOOKUP(PERCENTRANK.INC(IF((DataProducts=$A7)*(DataOrg=B$3), DataColC),DataColC),{0,0.05,0.95})=0.05)*(DataProducts=$A7)*(DataOrg=B$3),DataColC),FALSE))

enter with Ctrl + Shift + Enter

Change 0.05 in the above formula to 0 to get the average of the bottom 5% of values:
=AVERAGE(IFERROR(IF((LOOKUP(PERCENTRANK.INC(IF((DataProducts=$A7)*(DataOrg=B$3), DataColC),DataColC),{0,0.05,0.95})=0)*(DataProducts=$A7)*(DataOrg=B$3),DataColC),FALSE))

enter with Ctrl + Shift + Enter

(If needed, replace 0 with 0.95 to get the average for those values that are in the top 5%.)

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top