Microsoft Excel - WHAT IS THE FORMULA FOR DETERMINING A SERIES OF AVERAGES FOR A SPECIFIC NUMBER OF TIMES.

Asked By TOM LEASE on 15-Nov-12 12:13 PM
 I RATE EMPLOYEES EACH TIME THEY WORK FROM 1 TO 5 .  SOME EMPLOYEES WORK NEARLY EVERY DAY AND SOME WORK VERY SELDOM.  HOW DO I FIND THE AVERAGE RATING FOR THE LAST 10 TIMES  EACH HAS WORKED.  OVER A 6 MONTH PERIOD SOME ARE RATED 90 TIMES AND SOME MAY ONLY WORK 35 TIMES.  I WANT TO KNOW THE FORMULA OR FIND THEIR AVERAGE RATING OVER THE LAST 15 TIMES WORKED.
Harry Boughen replied to TOM LEASE on 15-Nov-12 05:45 PM
Hi Tom,

It will depend a bit on how the data is laid out.  Can you give a bit of an idea of what the dataset looks like?

Harry
Harry Boughen replied to TOM LEASE on 16-Nov-12 12:05 AM
Hello again Tom,

If your data looks something like
     B
   Jodie
   3
   4
   5
   3
   3
   etc

The following formula will work for you.

=AVERAGE(OFFSET($B$1,COUNT(B:B),0,-10,1))

If you want to vary the number of cells to average easily you can replace the 10 by a cell reference to a cell containing the required number .  Don't forget to leave the minus sign in the formula though.

Harry
TOM LEASE replied to Harry Boughen on 16-Nov-12 11:58 AM
Ok I try to explain.  column A has the employees names. Columns B thru the end (or 365 columns) has the days of the year.  Everytime a caddy works we ask the members to rate their experience with their caddy 1 to 5.  1 being bad and 5 excellent.  Over the course of the year we might want to see if some caddies ratings are improving or slacking off.  WHat I would like the formula to do is give me an average rating for the last say 15 days for each caddy.
i.e.  for this example lets find the caddies rating for the last 3 times he caddied.  
caddy 1 is in row 1 and he rating were as follows  B1 - 2,  C1 - 5, D1 - blank (he did not work that day), E1 - 5 and F1 - 4  was rated.  In G1 I would put may formula for average rating for the last 3 times he worked.  For this example it would be (5 + 5 + 4)/3 = 4.67 would be his rating.  D1 would not be counted because he did not work that day.  Then I would put that formula in suceeding row (G2, G3, G4 ....for each caddy).  Is that clear enough?  Thanks for your help.  REgards, Tom
Harry Boughen replied to TOM LEASE on 17-Nov-12 05:39 AM
Hello Tom,

This seems to work.  Don't ask me why, I picked it up elsewhere and haven't had time to work out the details.

=AVERAGE(INDEX(B1:NB1,,LOOKUP(9,COUNTIF(OFFSET(NB1,,-COLUMN(B1:NB1)+COLUMN(B1),,COLUMN(B1:NB1)-COLUMN(B1)+1),"<>"),COLUMNS(B1:NB1)-COLUMN(B1:NB1)+1)):NB1)

B1 to NB1 are your 365 columns.  The number at the start of the LOOKUP has to be one less than the number that you want to average.

Harry
TOM LEASE replied to Harry Boughen on 20-Nov-12 10:33 AM
Thank You Harry.  I'll give it a try and will let you know if I was successful.  Thanks Again.  Regards, Tom
TOM LEASE replied to Harry Boughen on 20-Nov-12 08:02 PM
Hi Harry, Its me again.  Well it was 98% successful.  Thank you again.  The one item that it did not calculate properly was when I had a caddy with 19 plays.  I asked for the average rating for the last 20 rounds.  As you suggested I put in 1 less at the start of the Lookup, so I put 19.  When I added a rating with the caddy with 19 rounds  it did not do anything.  It just showed #####.  When I added another round it did the same #####, but when I added a third round (or 22 rounds ) it calculated the average for the latest 20.  It really did not matter because most of the caddies were well over 20 rounds and the formula worked fantastic but at 20 and 21 rounds it would not calculate the average.  I noticed one thing that did not make a lot of sense in the formula you sent,  why at only one time do you type in COLUMNS and all the other places you use the word COLUMN.  Just curious????  Thanks again for you help.  Regards, Tom
Harry Boughen replied to TOM LEASE on 20-Nov-12 09:17 PM
Hello Tom,

I have tested it with 10 through to 30 entries and no hint of a problem.  Did you expand the column to see what the #### was meaning - might just be that the answer would not fit in the space available.

On your other point COLUMNS gives the number of columns in the range whilst COLUMN gives the column number.

Regards

Harry
TOM LEASE replied to Harry Boughen on 21-Nov-12 10:48 AM
Thanks again Harry