Would I use LOOKUP for this?

[Zakynthos]

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 ID’s’ (‘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

 

[Joel]

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 sheet
=(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

 

[Joe Mac]

The better toolset to use would be a Pivot Table to produce the ressults that
you desire... Select Data>PivotTable 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