Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula?
Worked like a charm! Thanks a bunch gang! -- Pondito ------------------------------------------------------------------------ Pondito's Profile: http://www.excelforum.com/member.php...o&userid=34792 View this thread: http://www.excelforum.com/showthread...hreadid=545473 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |