+ Reply to Thread
Results 1 to 2 of 2

Calculating the average(s) of top, middle and bottom quarter values

  1. #1
    Registered User
    Join Date
    Ljubljana, Slovenia
    MS-Off Ver
    Windows 7 Pro

    Calculating the average(s) of top, middle and bottom quarter values

    I have a large group of dairy cows producing milk, with different contents of fat and protein. I would like to calculate the average milk yield, fat and protein contents for the top 25 % of cows (a top quarter of all cows) according to the milk production. I would like to do the same calculation also for bottom quarter of cows according to milk yield, and for two middle quarters of cows. The number of cows producing milk is not constant but varies from milk control to milk control (monthly). (Eg. having 100 cows the calculations should refer to 25 cow with the highest milk yield, another calculation of the 25 cows with the lowest milk yield and two groups with 25 cows in each producing top middle nad bottom middle quantities of milk). I hope the question is clear
    Thanks, Andrej

  2. #2
    Forum Contributor
    Join Date
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010

    Re: Calculating the average(s) of top, middle and bottom quarter values

    Not the most perfect solution but it might inspire others to add to this.

    The biggest issue i see is when the number of records divided by 4 (to breakdown into the 4 groupings you want) is not exactly a whole number and does not breakdown the number of records into 4 equal parts.

    In the example provided we have 27 records, i then rounded up 27/4 to 7. Therefore the formula for the top 25%, the second 25% and third 25% will all result in the average being correct rows 2 thru 8 for top 25%, rows 9 thru 15 for second 25% and rows 16 thru 22 for third 25%. However for the bottom 25% rows 23 thru 27 are calculated
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. drop top and bottom values before computing average
    By jimboryan in forum Excel General
    Replies: 2
    Last Post: 07-15-2014, 04:04 PM
  2. Identifying top 33%, middle 33%, and bottom 33%
    By eaflynn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-18-2013, 09:15 PM
  3. Formula to return value for top, middle, bottom % values
    By brianjluke in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-13-2013, 03:05 PM
  4. [SOLVED] Get Top X,Middle Y,Bottom Z percent Records
    By Bala in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2006, 08:10 AM
  5. [SOLVED] calculating average on bottom 50% of data
    By steele57 in forum Excel General
    Replies: 1
    Last Post: 02-17-2006, 08:35 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1