Saturday, November 4, 2017

yogi_Compute Average Time Of Instrument And Combination Of Instruments Played

Google Spreadsheet   Post  #2274

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-04-2017
question by: Stumusic!topic/docs/_5eLD5eZ8MI;context-place=forum/docs
I need to use a logical "or" in AVERAGEIF but this does not seem to work
I teach band and I am running a little Harry Potter style "House Cup" competition. I have a spreadsheet that contains my rosters including the instrument each student plays. Their "Houses" Are broken up by instruments, flutes, clarinets, etc, but for some smaller sections multiple instruments are grouped together. I need to compute the Average grade percentage for each house. 

I tried AVERAGEIF(A1:A230, "Flute", G1:G230) and it works perfectly for averaging the flutes' scores. Likewise, using a wildcard character works for including both Bass Clarinet and Clarinet ("* Clarinet" gets both). However, AVERAGEIF(A1:A230, "Oboe" or "Bassoon",G1:G230) returns an error. 

The AVERAGEIFS function seems only to match multiple criterion by way of a logical AND, and I need a logical OR. The or operator is pretty darn handy. Is it just not supported in this program? (I also tried the || operator and this produced a parse error.) The OR function did not seem to work. AVERAGEIF(A1:A230,OR("Oboe","Bassoon"),G1:G230) does not work and I don't see any way to properly follow the OR function's syntax when nested inside AVERAGEIF.  

To ask more straight forward, in a list of names and ages for example, how do I find the average age of all the people named Dick or Richard?