Hi all,
I'm helping my girlfriend with her data and she wants an average over a group to display like the below table.
I've tried to simplify the example, but each row is basically the results of an experiment running on a machine and she wants the average of the outputted values. Is there a formula that will be able to calulate the average over each test group?
| Example | Value | Average (Desired Result) |
|---|---|---|
| Test_1 | 1 | 1.5 |
| Test_1 | 1 | 1.5 |
| Test_1 | 2 | 1.5 |
| Test_1 | 2 | 1.5 |
| Test_2 | 3 | 4.5 |
| Test_2 | 3 | 4.5 |
| Test_2 | 6 | 4.5 |
| Test_2 | 6 | 4.5 |
| Test_3 | 0 | 1.25 |
| Test_3 | 0 | 1.25 |
| Test_3 | 0 | 1.25 |
| Test_3 | 5 | 1.25 |
TIA for any help!
Videos
If I'm understanding your question, you could use AVERAGEIFS to accomplish what you are asking. With excel, a lot of how successful an approach will be is determined by how your data is structured, and if/how often you are planning on updating your work.
It would be easier to give a concrete answer with an example of the data you are looking at.
If your source data is in columns A:D something like:
and you are looking to summarize the weight data in I by account ID and Quarter:
you could use AVERAGEIFS(C:C,A:A,G2,B:B,H2) this would find the cells in column A that match the value in G2, find the cells in column B that match the value in H2, and report the mean value in column C of the matching rows.
An alternative is to use a pivot table, which automates a lot of what you are trying to do. For that approach you would select your data block, and click on Insert>>Insert Pivot Table (at least in my version). That should bring up a wizard. Accepting the defaults will create a new sheet. Then look on the right side of your window, and you should see a list of your column names near the top, and four boxes called Filters, Columns, Rows, and Values. You should be able to drag and drop your columns into these boxes to get summaries of your data. If you add your Account ID and Quarter tabs as columns, and your height and weight as Values, then right click on each of the value columns and select "Value Field Settings" and select Average from the menu that pops up. That should give you something that looks like:
At that point, you can change the formatting to make if fit your needs, or copy data somewhere else.
The AVERAGEIFS approach will automatically update if you add more data, but will only summarize things that match the values you list. If there is an account ID/Quarter pair that isn't in the summary column you won't have any idea it's there. If you are summarizing an ID/Quarter pair that isn't in your data you'll end up with a division by Zero error like in the example.
The Pivot Table option only updates when you manually click refresh (right click and choose refresh pivot table from the menu), but will summarize all the data based on the columns you've selected. It's also a little more robust as you avoid having to type out the formulas and make sure you are pointing to the right column. This option also by default provides nested summaries; you can turn the subtotals and grand totals off if you want.
I used the function:
=SUMPRODUCT(($B$2:$B$13="Customer 1")*($C$2:$C$13="Q1"))
Where Customer 1 could be a user id and Q1 you can change to which quarter you want.
Excel screenshot