• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Calculate an average automatically after score entry

Attached is an Excel 2007 spreadsheet of dates and quiz scores. New scores are always inserted so that the latest score is the first(Would not have to be this way). Would like to calculate the average score of the highest 5 scores out of the last 10 in one cell. If possible the formula would be dynamic. That is automatically adjust to the highest 5 of the last 10 after new entries. Have tried to use the OFFSET function to deescribe the range in conjunction with SUM but have not had any success. Can someone point me in the right direction? Thanks.
QuizSocores.xlsx
0
StampIT
Asked:
StampIT
  • 2
1 Solution
 
barry houdiniCommented:
Try this formula in A1

=AVERAGE(LARGE(OFFSET(A1,4,2,10),{1,2,3,4,5}))

regards, barry
0
 
barry houdiniCommented:
Sorry that would start the range at C5, should be

=AVERAGE(LARGE(OFFSET(A1,3,1,10),{1,2,3,4,5}))

that will give you the average of top 5 from B4:B13 even if more data is inserted

barry
0
 
StampITAuthor Commented:
Thanks. Works like a charm.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now