How can I get Excel to create multiple averages from a single table?
October 24, 2013 8:42 AM Subscribe
I have a table of data that I need to average, but I need to come up with averages using multiple different rows. For example average A=row 1, row 2, row 4: Average B= row 1, row 2, row 3: Average C= row 2, row 3. I would like to add a column that has the names of each average I need, (A, B, C) and then let excel average them based on which set they belong to. I have been looking around at other excel related forums, but I am not sure what this process is called, so I don't know how to find it. Right now I have been copying rows for each time they show up in a set I need averaged. It would be nicer to have column that says, this row belongs to set A and C, next one belongs to set A, B, and C.
Oh, I see I missed part of what you wanted, which is that any row might be in multiple averages. The simplest way to do this is to create a column for each Average, and put 1s and 0s in the column if that row should be in that average, and use averageif for each average based on if there is a 0 or a 1 in that column (you don't need quotes if you're comparing to a number instead of a string).
There are theoretically fancier ways to do this using "find", but honestly it's more trouble than it's worth. Just use 3 columns. You'll have a lot more flexibility to say easily find every row that is in Average B that way.
posted by brainmouse at 8:54 AM on October 24, 2013
There are theoretically fancier ways to do this using "find", but honestly it's more trouble than it's worth. Just use 3 columns. You'll have a lot more flexibility to say easily find every row that is in Average B that way.
posted by brainmouse at 8:54 AM on October 24, 2013
In case that was confusing... Let's say you have 3 rows. The first is in Average A only, the second is in A and B, and the third is in B and C, your rows would have 4 columns and look like: [i don't know how to do fixed width fonts so I'm just using "..." to divide the columns]
47...1...0...0
12...1...1...0
36...0...1...1
And then for Average A your formula would be:
=AVERAGEIF(B1:B3,1,A1:A3)
For Average B:
=AVERAGEIF(C1:C3,1,A1:A3)
And for Average C:
For Average B:
=AVERAGEIF(D1:D3,1,A1:A3)
posted by brainmouse at 8:57 AM on October 24, 2013 [1 favorite]
47...1...0...0
12...1...1...0
36...0...1...1
And then for Average A your formula would be:
=AVERAGEIF(B1:B3,1,A1:A3)
For Average B:
=AVERAGEIF(C1:C3,1,A1:A3)
And for Average C:
For Average B:
=AVERAGEIF(D1:D3,1,A1:A3)
posted by brainmouse at 8:57 AM on October 24, 2013 [1 favorite]
Response by poster: What I am trying to avoid is having to create a bunch of new columns since for some rows they are part of the same set and get averaged together, but for others, there are multiple sets where they belong. Therefore I would like just one column where I could "tag" each row and then use a pivot table to calculate averages based on common tags.
something like this:
data tag
47 A
12 AB
36 B
This would result in the following:
A= (47+12)/2
B= (12+36)/2
But where "47, 12, and 36" represent a whole row of 50 columns of data that need to get averaged this way.
posted by djpuddings at 9:12 AM on October 24, 2013
something like this:
data tag
47 A
12 AB
36 B
This would result in the following:
A= (47+12)/2
B= (12+36)/2
But where "47, 12, and 36" represent a whole row of 50 columns of data that need to get averaged this way.
posted by djpuddings at 9:12 AM on October 24, 2013
OK, well, you can't do this in a Pivot table, but you can get the averages the way you want. There are a few reasons I wouldn't suggest it, having to do with ability to look at your data and modify the averages things are in, but you can still do it with AverageIf and wildcards.
If you have 6 columns, and Column A is your data, and Column B is A, or AB, or ABC, or BC, or whatever, then for average A:
=AVERAGEIF(B1:B6,"*A*",A1:A6)
And Average B:
=AVERAGEIF(B1:B6,"*B*",A1:A6)
And Average C:
=AVERAGEIF(B1:B6,"*C*",A1:A6)
The "*"s are wildcards, so it will give you everything that contains A or B or C, regardless of what else is in the cell
But I really suggest using multiple columns instead -- you can do a pivot table that way if you want also, and it's much neater.
posted by brainmouse at 9:20 AM on October 24, 2013
If you have 6 columns, and Column A is your data, and Column B is A, or AB, or ABC, or BC, or whatever, then for average A:
=AVERAGEIF(B1:B6,"*A*",A1:A6)
And Average B:
=AVERAGEIF(B1:B6,"*B*",A1:A6)
And Average C:
=AVERAGEIF(B1:B6,"*C*",A1:A6)
The "*"s are wildcards, so it will give you everything that contains A or B or C, regardless of what else is in the cell
But I really suggest using multiple columns instead -- you can do a pivot table that way if you want also, and it's much neater.
posted by brainmouse at 9:20 AM on October 24, 2013
AVERAGEIFS may be what you need, since it will accept multiple criteria where AVERAGEIF only accepts one.
If each row already has a unique name (say in column A), you can create a column on your average page that contains all the names needed in the average and the criteria can be if the value in column A is in the cell containing all the names. I think then you could still use just AVERAGEIF.
posted by soelo at 9:21 AM on October 24, 2013
If each row already has a unique name (say in column A), you can create a column on your average page that contains all the names needed in the average and the criteria can be if the value in column A is in the cell containing all the names. I think then you could still use just AVERAGEIF.
posted by soelo at 9:21 AM on October 24, 2013
Also, the phrase "dynamic averages" might give you better search results.
posted by soelo at 9:23 AM on October 24, 2013
posted by soelo at 9:23 AM on October 24, 2013
Response by poster: Dynamic averages is getting closer to what I am looking for but not exactly. Here's a little more info:
The data I am using is soil test data. A field may have 20 soil samples taken in a grid pattern across the field. However, in many cases, the samples that were taken don't line up with where I split the fields into smaller management units or they could fall into multiple fields since they are close to a line and the lines are not exactly known, just drown on an aerial map.
I want to re-use some of the data points multiple times to come up with an average for smaller areas in the fields. (yeah, this gets into spatial statistics etc... but I don't want to have to go there) I don't have the actual spatial data to go with it, just a map showing rough location points which I use to decide which sub-field they go into.
Each row represents the data from one soil test with field, date, pH, organic mater, soil phosphorus, etc.... Sometimes a sample was taken on the line splitting two or more fields, so I want their data represented in multiple averages for each sub-field, East half, Northwest quarter, and Southwest quarter.
posted by djpuddings at 10:09 AM on October 24, 2013
The data I am using is soil test data. A field may have 20 soil samples taken in a grid pattern across the field. However, in many cases, the samples that were taken don't line up with where I split the fields into smaller management units or they could fall into multiple fields since they are close to a line and the lines are not exactly known, just drown on an aerial map.
I want to re-use some of the data points multiple times to come up with an average for smaller areas in the fields. (yeah, this gets into spatial statistics etc... but I don't want to have to go there) I don't have the actual spatial data to go with it, just a map showing rough location points which I use to decide which sub-field they go into.
Each row represents the data from one soil test with field, date, pH, organic mater, soil phosphorus, etc.... Sometimes a sample was taken on the line splitting two or more fields, so I want their data represented in multiple averages for each sub-field, East half, Northwest quarter, and Southwest quarter.
posted by djpuddings at 10:09 AM on October 24, 2013
Alright then I would definitely use multiple columns (1 for East, 1 for Northwest, 1 for Southwest -- a 1 in the column indicates the sample belongs to that region, a 0 indicates it does not, and a row can be in multiple columns) -- this simplifies a lot of things and allows you to manipulate your data better in the future and to use Pivot tables accurately (while what you're doing now -- making duplicates if it is in 2 areas -- does not, because then totals and counts are inaccurate).
But if you really need location to be in 1 column then my wildcard solution will still work, just not within Pivot tables.
Column A is your data, Column B is "East" or "East,Northwest" or "Northwest,Southwest" or whatever,
And then you can use =AVERAGEIF(B:B,"*Northwest*",A:A) to give you the average of Column A for all of the rows where Column B contains the word "Northwest".
posted by brainmouse at 10:18 AM on October 24, 2013
But if you really need location to be in 1 column then my wildcard solution will still work, just not within Pivot tables.
Column A is your data, Column B is "East" or "East,Northwest" or "Northwest,Southwest" or whatever,
And then you can use =AVERAGEIF(B:B,"*Northwest*",A:A) to give you the average of Column A for all of the rows where Column B contains the word "Northwest".
posted by brainmouse at 10:18 AM on October 24, 2013
Yeah, for extensibility and simplicity reasons, I would strongly recommend brainmouse's indicator-column-per-superset solution. Anything else has high potential to break quietly.
posted by PMdixon at 11:17 AM on October 24, 2013
posted by PMdixon at 11:17 AM on October 24, 2013
If you do end up using the 1 column and wildcard solution - be aware that you're going to need to pick fully unique names for each region. You won't be able to use "East" for one and "Northeast" for another because the match for *East* will match both of those.
The multiple column indicator is really the best route to go.
posted by ish__ at 11:30 AM on October 24, 2013
The multiple column indicator is really the best route to go.
posted by ish__ at 11:30 AM on October 24, 2013
This thread is closed to new comments.
Lets say you have 6 rows of numbers. Make column A your numbers, and column B your average names, say you call them Average1, Average2, Average3
then for your cell that will have the average of Average1, putting:
=AVERAGEIF(B1:B6,"Average1",A1:A6)
Will give you the average of column A for the rows where column B = "Average1"
posted by brainmouse at 8:49 AM on October 24, 2013