Help with formula?

[Pondito]

Ok, I am a bit perturbed by this problem I have been working in excel.
Here is the scenario:

I am running a report on last date worked "Jobs" for my reps. I
currently have the sheet setup so when our system reports a job was
worked it puts that in the "day 1" column and tommorrow that job slides
over to "day 2" etc. I have it setup to give me the percentage of these
jobs that are over/under a day threshold (for example if the rep has 3
out of 10 jobs older then the threshold of 4 days he/she is at 30%) but
what I am trying to achieve in addition is for the formula to be able to
tell me what the reps *average age per job is* (example: Rep Joe has 132
jobs in aging day 1, 76 in day 2, 54 in day 3, 189 in day 4, and 87 in
day 5, for the total of all Joe's 538 jobs what is his average job age?
Is it 3.5 days? 2.5? etc...

Any help that someone can provide will be GREATLY appreicated. Thanks!

 

[Guest]

I would use a weighted average here, mutliplying the number of jobs for each
job age in days, adding up, and dividing by the total number of jobs. In
your case:
=(132*1+76*2+54*3+189*4+87*5) / 538 = 3.04
It is easy to substitute each number for cell references. Of course, you
may use other formula if you prefer.

Hope this helps,
Miguel.

 

[Pondito]

Worked like a charm! Thanks a bunch gang!