compute an average if certain conditions are satisfied

Attach a sample workbook. (Not a picture!)
Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
Remember to desensitize the data.
Note:

• Please do not attach password protected workbooks/worksheets
• Please do not attach file(s) from exterior servers
• Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

20 rows of data is enough (probably)

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

See attached:

Formula

=SUMPRODUCT(($E$2:$E$9)*($A$2:$A$9=$G2)*($D$2:$D$9=OFFSET($A$1,MATCH($G2,$A$2:$A$9,0),1,1,2))/2)

Column G contains Student Names;

Hi John,

Many many thanks for your help. I'm so glad I asked my question because judging the formula you provided, I would never have figured this out on my own. I'm in the process of looking up all the functions embedded in your formula (I'm an Excel newbie) and try to make sense of their logic, and I hope I will understand this formula enough in order to apply it.

Thank you.

Anita

If you require any further help ..just ask!

And ....

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

John,

Sorry. I now have a basic understanding of each function (i.e., sumproduct, offset, and match) but still can't quite fully understand how all of those functions work together to produce the results. Would you kindly provide a translation of your formula into plain English?

Thanks!

=SUMPRODUCT(($E$2:$E$9)*($A$2:$A$9=$G2)*($D$2:$D$9=OFFSET($A$1,MATCH($G2,$A$2:$A$9,0),1,1,2))/2)

($E$2:$E$9) are the Grade scores

which we need to match with the Student ......

($A$2:$A$9=$G2) (returns TRUE if matched)

and we need to match the chosen subjects in columns B & C with those listed in column D ......

($D$2:$D$9=OFFSET($A$1,MATCH($G2,$A$2:$A$9,0),1,1,2))/2)

the OFFSET function matches the name (G2) against the names in A which gives us the (first) row number for the student

The first "1" in Offset positions us at Column B (offset 1 from A1), the second "1" is the height of one row and the 2 is the "width" (number of columns) we are including so this 2 for columns B & C (chosen grades): so we match data in column D against B & C for the selected row..

So for Mary we will compare D2:D9 with B2:C2 and James with B6:C6

Whenever we get a match (TRUE) this equates to value of 1 (FALSE = 0) so SUMPRODUCT multiplies and SUMs the grade scores by either 1 or 0 which results in SUM of the appropriate grade scores being returned.

The "/2" then just averages the result.

To see how this works, click on H2, then "Formulas"==>"Evaluate Formula"==>Evaluate.

FYI: the formula could also be defined as (OFFSET changed) ...

=SUMPRODUCT(($E$2:$E$9)*($A$2:$A$9=$G2)*($D$2:$D$9=OFFSET($B$1,MATCH($G2,$A$2:$A$9,0),0,1,2))/2)

Hope this helps.

Hi John,

Your formula and your explanation has been incredibly helpful. I think I understand it and will now apply it to my situation.

Thank you again for your help!