+ Reply to Thread
Results 1 to 2 of 2

Dynamic averageif and stdevif of sample sets

  1. #1
    Registered User
    Join Date
    Sydney, Australia
    MS-Off Ver
    365 Home

    Dynamic averageif and stdevif of sample sets

    I'm trying to create a table that will calculate the averages and standard deviations of a "Response" observed in samples and sub samples which are combinations of three categories that contain three variables as follows:
    - Culture: Strain A, Strain B, Strain C
    - Treatment: Treatment A, Treatment B, Treatment C
    - Temperature: 10, 20, 30

    The table which calculates the average and standard deviations is in a worksheet called "Dashboard" and references data in the worksheet "Results" for the calculations.
    I have worked out how to get the averages and standard deviations of all possible combinations individually but for my reporting I would also like to work out the averages and standard deviations if I isolate certain categories.
    For example if I am only interested in understanding the effect of two variables, lets say e.g. Strain A and temperature 10 regardless of treatment, on Response, I somehow want my table to work out the average and standard deviation for Culture = Strain A, Temperature = 10, and all treatment types. Sometimes I might only want to understand the effect of one variable such as Culture on response so I would be looking at standard deviations of samples Strain A, B and C regardless of temperature and treatment.

    I somehow managed to get around this for the averages by using averageifs and the "*" wildcard but the standard IF function which I used with stdev from what I've read doesn't recognise wildcards...
    You can play with this in the spreadsheet by changing any of the variables under "Culture" , "Treatment" or "Temperature" to *. You'll see that the "Number of specimens" and "Average response" gets updated but the "Stdev" column returns a #DIV/0 error.

    Is there anyway to make this table so that it can workout the average and standard deviations of whichever combination of categories and variables I choose without having to create multiple columns for each combination?? If possible, I would like just the one column for averages and one column for standard deviation which changes according to the combination of variables I choose. In addition, eventually I would like to include quartiles, mins and maxes in subsequent columns but I assume they would work very similar to the stdev function. This is so that I can turn this data into boxplots later kind of like on this site: support.microsoft.com/en-us/office/create-a-box-plot-10204530-8cdf-40fe-a711-2eb9785e510f

    Additional notes on how this workbook was intended to work:
    - The worksheet "Plan" contains all the categories and variables
    - "Results" worksheet contains the response data for all tested specimens. Inputs for the columns "Culture", "Treatment" and "Temperature" reference ranges in "Plan" to minimise my input errors.
    - "Dashboard" worksheet is meant to be a summary sheet for anyone who wants to see the sample averages and standard deviations at a glance. At the moment it has all possible combinations listed but the intention was more to just be able to choose what I wanted and only display that. It references the "Results" worksheet to determine the average response and standard deviations of all the listed combinations.
    - Also temperatures I've input as text e.g. '10, so that it works with the wildcard

    Hope this makes sense...
    I don't mind if this workbook needs to be changed completely but it was just my attempt at the solution. Appreciate any help with this one.

    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    MS-Off Ver
    H&B2016 & H&B2021

    Re: Dynamic averageif and stdevif of sample sets

    The SUBTOTAL function has the COUNT, AVERAGE and STDEV which you can use directly on your raw data table.

    Use FILTER to select whatever combinations you desire.

+ 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. Seeking Tutor for Replicating Unique Value, COUNTIF, AVERAGEIF and STDEVIF in VBA
    By VBA Novice123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-24-2021, 07:25 AM
  2. How to make data sets dynamic
    By Red4Roy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2020, 11:04 AM
  3. AVERAGEIF with dynamic range?
    By ceght in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2017, 08:17 AM
  4. [SOLVED] AVERAGEIF - Account for DIV/0, #NA and dynamic range
    By Dave@ExpSC in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-16-2014, 09:37 AM
  5. Using averageif with dynamic range
    By davetcw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2013, 07:17 PM
  6. STDEVIF Equivalent
    By Impartial Derivative in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2011, 11:48 AM
  7. [SOLVED] SumIF and STDEVIF is there a such thing
    By Chris in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2005, 12:05 PM

Tags for this Thread


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