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!

[Miguel Zapico]

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" wrote:

>
> 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!
>
>
> --
> Pondito
> ------------------------------------------------------------------------
> Pondito's Profile: http://www.excelforum.com/member.php...o&userid=34792
> View this thread: http://www.excelforum.com/showthread...hreadid=545473
>
>

[Pondito]

Worked like a charm! Thanks a bunch gang!