3

So, I have this problem, I would like to find the average of a column by using the OR function to check criteria from adjusted columns, I tried putting OR into AverageIf function, fail, also tried the "Average(IF(OR(" again not the correct return. Thought it is a simple thing could be done easily but don't know why it doesn't work. So my table is something like this: 

ID:  Rate   Check 1 Check 2 Check 3  
1    5         1       1       1
2    3         1               1
3    2                 1
4    4
5    5         1       1
6    3    
7    4         1

I would like to find the average of the rate column by checking if there are any value in either Check 1; Check 2 or Check 3 columns, so in the above case i will get the average of all but row with the id 4 and 6. Is this possible without using a helper column?

elv
  • 85
  • 6

2 Answers2

5

You can use SUMPRODUCT()

=SUMPRODUCT(((C2:C8<>"")+(D2:D8<>"")+(E2:E8<>"")>0)*(B2:B8<>"")*B2:B8)/SUMPRODUCT(--((C2:C8<>"")+(D2:D8<>"")+(E2:E8<>"")>0)*(B2:B8<>""))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    Can you explain or give a link which explain the -- in the formula? – Pierre44 Jun 01 '18 at 14:03
  • 2
    @Pierre44 - it's called a [double unary](https://www.google.ca/search?q=double+unary). –  Jun 01 '18 at 14:10
  • Thanks @Jeeped, can I get average from the entire column with this formula, since my data is likely to grow ? I tried something like this but didn't work: =SUMPRODUCT(((Solution!C:C<>"")+(Solution!D:D<>"")+(Solution!E:E<>"")>0)*Solution!B:B)/SUMPRODUCT(--((Solution!C:C<>"")+(Solution!D:D<>"")+(Solution!E:E<>"")>0)), any idea what it could be? – elv Jun 01 '18 at 14:21
  • @elv you will want/need to limit the range to that of the data. SUMPRODUCT is iterative and it will slow down the calculations. Set it to the largest the table will probably reach. I doubt it would every reach 1 million rows. – Scott Craner Jun 01 '18 at 14:29
  • Thanks, I have one more follow up question, in some cases, I have a respondent checked in one of the columns(C, D, E) but haven't given any rate, so empty in column B. In this cases I get the average wrong, with this formula, how can I ignore this value from the average? – elv Jun 01 '18 at 15:04
  • remember to choose one of the answers as the correct one by clicking the check mark by the answer, choose the one that you use. make sure you look at and try both. @elv – Scott Craner Jun 01 '18 at 15:25
2

If your first ID starts in A2, use this formula (edited to handle empty values in the "Rate" column):

=AVERAGE(IF(MMULT(LEN(C2:E8)*LEN(B2:B8),ROW(INDIRECT("1:"&COLUMNS($C$1:$E$1)))),B2:B8))
jblood94
  • 10,340
  • 1
  • 10
  • 15
  • The question then becomes if `0` in column B should be included or not. with your edit `0` is the same as a blank cell and will be excluded from the average. That may be what the OP wants, just putting it out there for an FYI. Otherwise I like this formula better than mine and wonder why it has not got more upvotes. – Scott Craner Jun 01 '18 at 15:20
  • @ScottCraner Good point. The OP only ever mentioned blank cells. Updated the answer with `LEN` dropped in around the arrays in the first argument in `MMULT`. – jblood94 Jun 01 '18 at 15:40