AVERAGE function using arrays

  • Thread starter Thread starter WANDO
  • Start date Start date
W

WANDO

I have to calculate the average results of user surveys,
but only if two conditions are satisfied. The
spreadsheet has the names of the respondants in the far
left column, and next column has the first of the two
conditions that has to be satisfied (its "yes" or "no").
The second condition that must be satisfied is a column
heading. The problem is that when I use the AVERAGE
function in an array formula (to 'ask' the two
questions), a 'false' result on the second conditions
returns a '0' value, and is therefore counted in
calculating the average when it should not be. Using the
SUM function in the same array returns the correct
answer, but the COUNT funtion using the same array does
not...so I can't do the math myself either. Any
suggestions?
 
WANDO,

Use a formula like this, array entered (Ctrl-Shift-Enter):

=AVERAGE(IF((A1:A10="WANDO")*(B1:B10="Yes"),C1:C10))

The "WANDO" can also be a cell reference.

HTH,
Bernie
MS Excel MVP
 
Thanks for the quick response. I did try your formula
suggestion, but unfortunately I kept getting a DIV/0
error. I beleive one of the contitions is still being
evaluated as a false and therefor a zero. I thought if
you saw a sample of the table, you might be able to
better help me out. I need to average the scores for
each category (A,B,C), but only if the Valid? column
returns a "yes".

B A C C A
Name Valid ? Q1 Q2 Q3 Q4 Q5
Name 1 Yes 5 5 4 3 4
Name 2 Yes 5 5 2 3 4
Name 3 Yes 5 5 5 5 5
Name 4 No 3 5 3 5
Name 5 Yes 5 5 3 5 5
Name 6 Yes 3 5 3 4 4
Name 7 No 4 5 4 4
Name 8 No 2 2 2 2

Thanks in advance!
 
Back
Top