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
Bookmarks