# Dynamic averageif and stdevif of sample sets

1. ## Dynamic averageif and stdevif of sample sets

Hi,
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.

Thanks!
-Yuki

2. ## 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.

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