Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    last 6 averaged (Excel)

    I need a way average the last 6 scores for each employee. I have the employee name, date, score, team and evaluator. The data sheet starts from the first of the year and each employee can receive between 2 to 6 scores each month. Can someone offer a way to accomplish this. I would like to have a second page with all the employees and their averages that would update when new added to the data sheet. I would be thankful for any help on this. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    As any formula or code ythat might be required would be dependent on the layout of you data, could you post a worksheet with a few sample entries, otherwise we can only guess what might be appropriate.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    This formula will average the last 6 values in column A:

    <pre>=AVERAGE(OFFSET(A1,COUNTA(A:A)-1,0):OFFSET(A1,COUNTA(A:A)-6,0))
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    Here is an example of the sheet. I need an average of the last 6 scores for each employee. Thanks for looking at it for me.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    I think you forgot to attach the example.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    Lets Try it again. here is the attachment
    Attached Files Attached Files

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    Jim,

    To achieve what you are looking for, I inserted two extra columns (you can hide them if you wish) and included a pivot table on second sheet (Averages).

    A Worksheet_Change event updates everthing once a new row is inserted. The update is triggered once the name of the evaluator is inserted so that entry should be made last. There is no need to make any entries for the new columns as the update procedure handles that.

    As it is setup, it is important that no data other than the score records is included on the worksheet.

    I think a userform approach might be better than the change event, so if you would go with that it can be adapted. The file is attached - zipped as it exceeds 100kb

    Andrew C
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    With that layout, it will be tough to do this with a formula. I could probably figure out how to do it, but it would be a complex formula. It is much easier to write a user defined function. I have attached your example with a user defined function that calculates the average. I have also added a new sheet to show how you might use it. The new file was too big to upload so I had to delete some of the data from your sheet.
    Attached Files Attached Files
    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    Thank you for the help I think this will work for me. I was looking at maybe a count loop to copy the the last 6 for each employee to a new sheet and doing a pivot table from that. This will work better because all I would have to do is add to the bottom. Thanks again.

  10. #10
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    Thanks for your help, I think I have a good start in creating the report I need. Thanks

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    I am attaching an amended version which does not use change events, but requires that an update macro be run when any changes are made. A macro button is included to simplyfy. When you add a new entry, just click th ebitton and everything updates. Also if you edit an existing entry, you can click update, whereas the previous version only updated with a new entry.

    Andrew
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Jun 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last 6 averaged (Excel)

    Excuse are you a mind reader also. I import the data from another application and add as many as 50 new entries daily as a cut and paste. I was planing to update or extend the formulas manually. Thanks again. Everyone here is always so helpful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •