Taking average of certain values in one Excel column based on values in another

0
votes

I have a (large) array of data in Excel of which I need to compute the average value of certain values in one column, based on the values of another column. For example, here's a snippet of my data:

enter image description here

So specifically, I want to take the average of the F635 mean values corresponding with Row values of 1. To take it a step further, I want this to continue to Row values of 2, Row values of 3 etc.

I'm not familiar with how to run code in Excel but have attempted to solve this by using the following:

=IF($C = "1", AVERAGE($D:$D), "")

which (to my understanding) can be interpreted as "if the values (anywhere) in column C are equal to 1, then take the average of the corresponding values in column D."

Of course, as I try this I get a formula error from Excel.

Any guidance would be incredibly appreciated. Thanks in advance.

2
excelvba

2 Answers

0
votes

For more complicated cases, I would use an array-formula. This one is simple enough for the AVERAGEIF formula. For instance =AVERAGEIF(A1:A23;1;B1:B23)

Array-formula allows for more elaborate ifs. To replicate the above, you could do =SUM(IF($A$1:$A$23=1;$B$1:$B$23;0))/COUNT(IF($A$1:$A$23=1;$B$1:$B$23;0)). Looks like more work but you can create extremely elaborate if-statements. Instead of hitting ENTER, do CTRL-ENTER when entering the formula. Use * between criteria to replicate AND or + for OR. Example: SUM(IF(($A$1:$A$23="apple")*($B$1:$B$23="green");$C$1:$C$23;0)) tallies values for green apples in c1:c23.

Your sample data includes three columns with potential ifs so my guess is that you're going to need array formulas at some point.

0
votes

Excel already has a builtin function for exactly this use; AVERAGEIF(). 

=AVERAGEIF(C:C,1,D:D)