0

I have the data below

   A       B
1  3       3
2  2       5
3  1       2
4  3       
5  3

I want to take the average of column B and if column A contains a value of 3 that is unmatched in its corresponding row in column B, include it in the average calculation. So the value of 3 from row 1 would be excluded because it is matched in column B, but the value of 3 from row 4 and 5 would be included to make it the average of 3(B1),5(B2),2(B3),3(A4),3(A5). What I have is below, but it adds a value of 0 to the average if the COUNTIFS statement comes out to be 0, which skews the average down.

AVERAGE(B1:B5, 3*COUNTIFS(A1,3,B1, "<>3"), 3*COUNTIFS(A2,3,B2, "<>3"),3*COUNTIFS(A3,3,B3, "<>3"),3*COUNTIFS(A4,3,B4, "<>3"),3*COUNTIFS(A5,3,B5, "<>3"),)
User247365
  • 665
  • 2
  • 11
  • 27
  • how did you come up with 3,5,2,3,3 ? – Gowtham Shiva Sep 08 '17 at 16:12
  • B1, B2, B3, A4, A5 – User247365 Sep 08 '17 at 16:13
  • and this ? `the value of 3 from row 1 would be excluded because it is matched in column B` – Gowtham Shiva Sep 08 '17 at 16:14
  • That is what I am wanting to accomplish, that if 3 exists in column A and is also in the same row in column B, do not include the value of 3 from column A in the calculation of the average. If this condition did not exist, I could just do something like if the value 3 is in column A, include it in the average calculation, but the way the data is set up, I can't do that. I need it to contribute to the average only once. – User247365 Sep 08 '17 at 16:17
  • Why was that 3 included in 3,5,2,3,3 ? – Gowtham Shiva Sep 08 '17 at 16:18
  • because that first 3 is from column B, I still want it included there. I am trying to take the average of column B, and if a value of 3 exists in column A that is unmatched in the same row of column B, include that value of 3 from column A in the average calculation of column B. – User247365 Sep 08 '17 at 16:20
  • still confusing. What are you excluding then? I see nothing that is excluded – Gowtham Shiva Sep 08 '17 at 16:23
  • So if B1 were `5` we would take the `3` from A1 instead of the `5` from B1? – Scott Craner Sep 08 '17 at 16:54
  • If I said that any value of 3 from column A was included in the average calculation, A1, A4 and A5 would all be included in the average calculation because they contain a value of 3, but I want A1 to be excluded because a value of 3 exists in B1. The values of 3 that exist in A4 and A5 should be included because they are unmatched in B4 and B5 respectively. – User247365 Sep 08 '17 at 16:56
  • @ScottCraner Not instead of, in addition to. I want the average of every cell in column B regardless. I also want a value of 3 to be included in the average calculation if it exists in column A and is unmatched in the same row of column B – User247365 Sep 08 '17 at 16:57
  • So What would you expect if you changed Column A to {1,2,3,4,5} and B stays the same, what numbers would be averaged? – Scott Craner Sep 08 '17 at 16:59
  • In that case I would want the average of only the contents of column B – User247365 Sep 08 '17 at 17:02
  • So, even though row 3 has 3 in A since it has a value in B and it is not three we ignore it and do the value of B? Sounds like you want all of B and any 3's in A where B is null. – Scott Craner Sep 08 '17 at 17:05
  • Yes the more that I am talking through it, that is a better way of thinking about it. – User247365 Sep 08 '17 at 17:06

1 Answers1

1

Use this array formula:

=AVERAGE(IF(B1:B5<>"",B1:B5,IF(A1:A5=3,3)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

![enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81