| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Would I use LOOKUP for this?
I would like to use a formula (LOOKUP?) to enable me to do the following:
In column A: I have the time in 15 minute time intervals In column B: I have variable groups called route IDs (a, b, ;c; etc) In column C: I have call volumes per 15 minute period for each route ID In columns L & N: I have 2 sets of data which when summed will give me the call handling time for that 15 min period for that route ID. The info is organised as follows A B C ¦¦¦. L N Time Route ID Call volume CHT1 CHT2 08:00 a 10 778 521 0800 a 15 498 615 0800 b 14 769 1021 0800 a 17 28 1400 0800 b 25 269 743 0800 a 18 890 1005 0815 b 21 777 650 0815 a 45 900 740 0815 a 46 210 367 0815 b 74 456 789 etc What formula/function would I need and how would this be applied to the above to give me: 1. The average call volume for each 15 minute period for a particular route ID (say called a, b, c etc) 2. The average call handling time (CHT) for each 15 minute period for a particular route ID Many thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Would I use LOOKUP for this?
If the data above is on Sheet1 and the Summary is on Sheet 2 th eformulas
below will work.. On sheet 2 put in column A and b each unique combination of Time and Route ID Sheet 2 A B C D Time Route ID Total Call Volume Average 0800 a 0800 b 0815 a 0815 b Sheet1!A1:A100 is the size of the TIME and A1 is the time on sheet2. Sheet1!B1:B100 is the size of the Route ID and B1 is the Route ID on sheet2. Call volume in sheet2 column C =sumproduct(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=B1),Sheet1!C$1:C$100) Average Call Handling in column D on sheet2 =(sumproduct(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=B1),Sheet1!L$1:L$100) + sumproduct(--(Sheet1!A$1:A$100=A1),--(Sheet1!B$1:B$100=B1),Sheet1!N$1:N$100)/C1 "Zakynthos" wrote: I would like to use a formula (LOOKUP?) to enable me to do the following: In column A: I have the time in 15 minute time intervals In column B: I have variable groups called route IDs (a, b, ;c; etc) In column C: I have call volumes per 15 minute period for each route ID In columns L & N: I have 2 sets of data which when summed will give me the call handling time for that 15 min period for that route ID. The info is organised as follows A B C ¦¦¦. L N Time Route ID Call volume CHT1 CHT2 08:00 a 10 778 521 0800 a 15 498 615 0800 b 14 769 1021 0800 a 17 28 1400 0800 b 25 269 743 0800 a 18 890 1005 0815 b 21 777 650 0815 a 45 900 740 0815 a 46 210 367 0815 b 74 456 789 etc What formula/function would I need and how would this be applied to the above to give me: 1. The average call volume for each 15 minute period for a particular route ID (say called a, b, c etc) 2. The average call handling time (CHT) for each 15 minute period for a particular route ID Many thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Would I use LOOKUP for this?
The better toolset to use would be a Pivot Table to produce the ressults that
you desire... Select DataPivotTable and PivotChart Wizard... Enter the range of the data that you desire to use (a PivotTable requires all of the data to be in a contiguous range with Column Headers for each column)... use the deafult values to let Excel insert a new worksheet with the PivotTable... The beauty of a PivotTable is that you can manipulate the data to the set of desired results without establishing formulas ... Drag the column headers to the desired drop areas... for example drag the Time column header over the the Row Drop Area... Drag the column headers to the desired drop areas... for example drag the Route column header over to the over the the Column Drop Area and finally drag the Volumn column header over to the Data drop area... Joe -- Joe Mac "Zakynthos" wrote: I would like to use a formula (LOOKUP?) to enable me to do the following: In column A: I have the time in 15 minute time intervals In column B: I have variable groups called route IDs (a, b, ;c; etc) In column C: I have call volumes per 15 minute period for each route ID In columns L & N: I have 2 sets of data which when summed will give me the call handling time for that 15 min period for that route ID. The info is organised as follows A B C ¦¦¦. L N Time Route ID Call volume CHT1 CHT2 08:00 a 10 778 521 0800 a 15 498 615 0800 b 14 769 1021 0800 a 17 28 1400 0800 b 25 269 743 0800 a 18 890 1005 0815 b 21 777 650 0815 a 45 900 740 0815 a 46 210 367 0815 b 74 456 789 etc What formula/function would I need and how would this be applied to the above to give me: 1. The average call volume for each 15 minute period for a particular route ID (say called a, b, c etc) 2. The average call handling time (CHT) for each 15 minute period for a particular route ID Many thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Hybrid Mode
