Hello,
I'd like to return an average by matching the text in the small table to a row of cells in my larger table of test scores.
Once the row has been identified I'd like to average the values for a certain period using several criteria, for example >=11 and <=15 within the formula for easy amending this going forward, but this is not essential.
Looking forward to hearing from you.
Last edited by TripleXL; 03-27-2012 at 11:51 AM.
You can put this array* formula in cell O20 of your workbook:
=AVERAGE(IF(($C$5:$C$16=$N20)*($D$4:$AB$4>=--LEFT(O$19,FIND("-",O$19)-1))*($D$4:$AB$4<=--RIGHT(O$19,LEN(O$19)-FIND("-",O$19))),$D$5:$AB$16))
and then copy it across and down to fill your small table.
*Note: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula subsequently, then you will need to use CSE again.
If you want to add another (4th) criteria then you can do so by adding this:
*(criteria4)
to the string of criteria already there. The asterisk is equivalent to AND in this situation.
Hope this helps.
Pete
Excellent, I'm relieved to see that this can be done. I'm not sure I would have come up with this method of doing it.
The test scores actually look more like these on ef68_Test Scores_2.xlsx as they are often missed. The formula above works perfectly with a full data set but when one or more values are missing the formula seems to divide the total score for each period by 5 rather than by the number of tests actually taken within this period.
Would this mean a completely new formula is required or a small amendment to what is suggested above?
Thanks again
I've added another condition to the formula in O20:
=AVERAGE(IF(($C$5:$C$16=$N20)*($D$4:$AB$4>=--LEFT(O$19,FIND("-",O$19)-1))*($D$4:$AB$4<=--RIGHT(O$19,LEN(O$19)-FIND("-",O$19)))*($D$5:$AB$16<>0),$D$5:$AB$16))
entered it with CSE, and then copied across and down as before. The new condition ensures that blank cells (and zeros) are not counted within the average - if zero is a valid score (different to blank) then you can change the <>0 to <>"" in the fourth condition.
Hope this helps.
Pete
If this has solved your problem please mark the thread as Solved (the FAQ tells you how to). Also, please click on the "star" icon in the bottom left corner of any post that has helped you.
Last edited by Pete_UK; 03-27-2012 at 11:26 AM. Reason: changed colour
That's great, thanks a lot for taking another look! Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks