+ Reply to Thread
Results 1 to 6 of 6

How to display averages, min and max from varying data sets and sub groups

  1. #1
    Registered User
    Join Date
    06-13-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    3

    How to display averages, min and max from varying data sets and sub groups

    Hi all,

    I'm new to excel forum, however I have a query that I need solved as soon as possible. I'm not sure if a VBA or macros would be more suitable, however I'm looking for an answer by just using formulas.

    Within my data, I have specific 'geology codes', and within each geology code, I have specific 'soil types', and within each soil type, I have varying 'densities / consistencies', and within each of these, I have a lab test result. I need a formula to present the minimum, maximum and average lab result for each density / consistency, of each soil type, within each geology code...

    is there a way this can be done?

    Example: Geology Code = Alc, for Alc I have 5 different soil types, for each soil type, I have 2 to 4 different densities / consistencies, and for each of these, I have a set of lab data... I need the min, max and average for each...

    Any help would be greatly appreciated.

    Thanks

    shoey_123

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to display averages, min and max from varying data sets and sub groups

    Hi,

    Welcome to the forum

    You will get quick and exact solution if you attach a sample workbook with expected output for better understanding


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to display averages, min and max from varying data sets and sub groups

    Hi and welcome to the forum

    It would probably be easier to offer suggestions to help you, if we could see a few samples of what you are working with, along with a few examples of what answers you would expect and hopw you arrived at them.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-13-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to display averages, min and max from varying data sets and sub groups

    Thanks Sixthsense and FDibbons,

    I have attached an example.... You can see that I have two different 'geology codes' (Alc and Ca), and you can see for each 'geology' code that I have two different 'soil types' (CLAY and SAND), for each soil type, there are varying 'consistencies / densities' (i.e. Loose, Medium Dense, Soft, Firm, just to name a few), and the last column shows 'N' (which is essentially the lab result), which are just varying numbers.

    So, my expected output would to have a minimum, maximum and average 'N' result for each 'consistency / density' type, for each 'soil type' within each 'geology code'... i.e. I want the output over 6 columns, saying 'Alc', 'CLAY', 'Soft', 'N minimum', 'N maximum', 'N average'.

    I think it would be like a heap of IF loops etc embedded into one another (well, I actually have no idea) - or I'm guessing, I would have to write a code or something like that...

    Any help would be greatly appreciated.

    Many Thanks

    Shoey_123
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-13-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to display averages, min and max from varying data sets and sub groups

    Like I know I can manually filter the results, and then find the averages, min and max for each and then write them down, but I'm sure this can be done in Excel.

    Thanks

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to display averages, min and max from varying data sets and sub groups

    Hi Shoey and welcome to the forum,

    See if this Pivot table gives you the data you need.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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