2

I have a following data set. I want to get, in column E, average of column B values , if the sample and gene are same using something like AVERAGEIF function in Excel.

For example, in E2 cell, I want to have the the average of B2 and B7 since they have same value in column C i.e "alpha", and column D i.e "S14".

Here is the screenshot of sample data:

enter image description here

kolcinx
  • 2,183
  • 1
  • 15
  • 38
alpha189
  • 93
  • 1
  • 8
  • 1
    You would need AVERAGEIFS(): https://support.office.com/en-us/article/AVERAGEIFS-function-48910c45-1fc0-4389-a028-f7c5c3001690?ui=en-US&rs=en-US&ad=US&fromAR=1 – Scott Craner Dec 15 '16 at 16:04
  • ya, I think I need that, but I am not sure about the formula for such cases – alpha189 Dec 15 '16 at 16:07
  • The link I provided give a good tutorial on how to use it. It will be much more detailed than anything we can give. – Scott Craner Dec 15 '16 at 16:08
  • I have checked it, but I am not sure how can I define "criteria" saying that values in column(x) are equal/same. – alpha189 Dec 15 '16 at 16:13

1 Answers1

3

You would need AVERAGEIFS()

The Formula would be:

=AVERAGEIFS(B:B,C:C,C2,D:D,D2)

Put it in E2 and copy/drag down.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • @alpha189, Scott used simplyfied arguments (whole columns references) for the function. After you get it, you can use more explicit `=AVERAGEIFS($B$2:$B$12,$C$2:$C$12,$C2,$D2:$D12,$D2)` – kolcinx Dec 15 '16 at 16:26
  • 1
    @BranislavKollár This is not an array formula and therefore does not have any detriment to leave it as full column references. I would leave it as such. – Scott Craner Dec 15 '16 at 16:28
  • Interesting point. Can you please expand/provide a resource, where i can learn more about when you can/shouldn't use full column references? – kolcinx Dec 15 '16 at 16:34
  • The formulas SUMIFS(),AVERAGEIFS,... Were specifically designed to avoid the detriment of array formulas. Formulas such as SUMPRODUCT(),AGGREGATE() are array type formulas as well as any formula that requires the confirmation of CTRL-SHIFT-ENTER. These formulas should be confined to the data set and not full column. – Scott Craner Dec 15 '16 at 16:38
  • Thank you, sir. I have found an [article about Excel performance on MSDN](https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx#xlAllowExtraData) with even more detail. – kolcinx Dec 15 '16 at 17:03
  • Also [Excel 2010 Performance: Tips for Optimizing Performance Obstructions](https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx#xlAllowExtraData) – kolcinx Dec 15 '16 at 17:10
  • @ScottCraner just in case, would it be also possible to get standard deviation of the mean that would be obtained ? I couldn't really find it from google searches :-( – alpha189 Dec 15 '16 at 21:53
  • @alpha189 you would need to use an array formula. That is a new question. – Scott Craner Dec 15 '16 at 21:55
  • @ScottCraner I have to admit that I am new to Stackoverflow. Should I ask new question if I want to know standard deviation of same data set? – alpha189 Dec 15 '16 at 21:57