Assistance completing a project requested - pulling average data from other tabs

[William Hulme]

Hi everybody,

Please can I ask for some further assistance.

I have been given a project in work and I have had some help via this forum which has just been fantastic. The result of the assistance has shown me some capabilities in Excel that I did not know it could do.

Firstly thanks and credit to a members JawaharPrem, p45cal and particularly Bosco for their insight and assistance in getting me this far. (Bosco - I still don’t understand the aggregate function!)

Since receiving the information provided I’ve started to play with it to mould it closer towards something that I need as I now have more of a brief than I did at the first time of asking. I’ve attached my current position to this message.

The spreadsheet is to be used by managers of varying levels for weekly and / or monthly 121 discussions regarding quality and quantity of work.

How I need the spreadsheet to work is as follows:-

1. I maintain a list of Agents, Team Managers, the more senior Operations Managers and possibly Department names on the helper tab so metrics can be attributed to the correct people on the correct teams.

2. On the WTD (week to date) and MTD (month to date) tabs I extract data (namely AHT (average handling time), Engage Time, Hold Time, Wrap Time and NPS (a measure of customer satisfaction)) from other systems and paste these in to the respective columns.

3. On the TM Dashboard a Manager will select his or her name from the drop box and the data for all of his or her team members is shown in the WTD and MTD tables. I have put the makings of some graphs on this page albeit the one for Call Time Distribution is not pulling all agents – Team Manager 1 has 14 agents and it pulls 14, TM2 has 15 but it still pulls 14 and I cannot see why – any thoughts? Team size can fluctuate so I just need it to pull the data for however many people are in each team at that time.

4. The TM can talk to the individual agents in his or her team using the data pulled through from the WTD and MTD tabs. They will see the whole team and as such can make comparisons.

5. The Operations Managers (OM’s) will use their tab when discussing team achievements with the TM’s. When they select their name, the table will return the averages of the categories measured for each Manager in their charge, that is to say, the averages for AHT, Engage Time, Hold Time, Wrap Time and NPS for Manager 1, Manager 2 etc. I will be putting similar graphs in place on that tab later.

6. The level of staff in the entire department swells and shrinks at various times of the year such as before and after Christmas and other significant dates in the calendar and to this end it would be useful for the spreadsheet to be able to grow and shrink as agents step up to be temporary Managers and new temporary agents are taken on.

At the moment the data can be pasted in and the WTD tab will pull it together. The MTD tab is an addition since last time. Data can be pasted in there and it will also pull through. I’ve replicated the graphs for WTD and MTD. The OM Dashboard tab is not working properly. When I select an OM, I get the individuals reporting in to TM1, TM2 etc and not an average of the metrics for each TM.

Please can anybody help with this?

Many thanks

Will

 

[bosco_yip]

.........
- The OM Dashboard tab is not working properly.
- When I select an OM, I get the individuals reporting in to TM1, TM2 etc and not an average of the metrics for each TM......
Will

1] In the OM Dashboard tab, I notice that your formulas have not copied down fully, it must be copied down until blank.

2] Give more information regarding "an average of the metrics for each TM", and give examples of your expected result.

Regards
Bosco

 

[William Hulme]

Hi Bosco,

Many thanks for getting back to me.

What I am looking for on the OM Dashboard tab is as follows:-

1. The OM selects his or her name so for example if we use Steve Wilson as an example, he selects his name from the drop down box then in column A under the Team Manager heading I am looking to see 1 row for Manager 1, 1 row for Manager 2, 1 row for Manager 3 and so on until all Team Managers reporting to Steve Wilson have a line. The same would be true for the other OMs when they select their names.

2. The data for each team Manager will pull through as an average for each TM under the respective heading so for example, if we take Manager 1, he or she has 14 agents in the WTD and MTD tabs. Under AHT for Manager 1 I am looking for an average of those 14 people which equates to 479, for Engage Time 387, Hold Time 75, Wrap Time 17 and NPS 23

3. On the OM tab, under the row of data for Manager 1 I would like to see Manager 2 and then the corresponding data for him or her, and then the same again until all of Steve Wilson's Managers have been referenced.

4. When the next OM Craig Lynn gets the same dashboard and selects his name, the TM data changes and shows the corresponding information for his Managers only.

5. I am then looking to input graphs similar to those in the TM Dashboard to keep consistency in appearance running throughout.

Regarding the copying down of the formulae, I'm a bit unclear of what to do here. The table on the OM tab is populated with the formula from top to bottom and the data displayed is Manager 1x14 and then Manager 2 for the remainder of the table. As it is fully populated I'm not sure what needs dragging down although it is pulling agent data as opposed to the average for each Manager.

Does this help at all or have I confused the issue further?

Kind regards

Andy

 

[vletm]

William Hulme
Would You check this version?
Even now all 'Team Managers' will come to 'OM Dashboard'-sheet.
... and no matter how many of those would be there.

 

[William Hulme]

Hi vletm!

Thank you for taking the time to look at my query.

You are correct, the Team Manager data is populating the OM Dashboard but unfortunately I'm sorry to say that this is not what I am looking for.

I was hoping to get this in to a position whereby the categories measured for each agent average out so for example, on the OM Dashboard tab, when the first OM name is selected, the first row reported is the first Team Manager reporting in to him or her. The AHT for that Team Manager is an average of the 14 agents he or she is responsible for. The other categories would follow suit. Looking to the second row, this would be an average of the various categories for Manager 2's 15x agents and so on for all Managers reporting to that OM.

I hope this makes sense but again thank you for taking time to look in to my query.

Kind regards

Will

 

[bosco_yip]

Hi Bosco,

Many thanks for getting back to me.

What I am looking for on the OM Dashboard tab is as follows:-

1. The OM selects his or her name so for example if we use Steve Wilson as an example, he selects his name from the drop down box then in column A under the Team Manager heading I am looking to see 1 row for Manager 1, 1 row for Manager 2, 1 row for Manager 3 and so on until all Team Managers reporting to Steve Wilson have a line. The same would be true for the other OMs when they select their names.

2. The data for each team Manager will pull through as an average for each TM under the respective heading so for example, if we take Manager 1, he or she has 14 agents in the WTD and MTD tabs. Under AHT for Manager 1 I am looking for an average of those 14 people which equates to 479, for Engage Time 387, Hold Time 75, Wrap Time 17 and NPS 23

3. On the OM tab, under the row of data for Manager 1 I would like to see Manager 2 and then the corresponding data for him or her, and then the same again until all of Steve Wilson's Managers have been referenced.

4. When the next OM Craig Lynn gets the same dashboard and selects his name, the TM data changes and shows the corresponding information for his Managers only.

5. I am then looking to input graphs similar to those in the TM Dashboard to keep consistency in appearance running throughout.

Regarding the copying down of the formulae, I'm a bit unclear of what to do here. The table on the OM tab is populated with the formula from top to bottom and the data displayed is Manager 1x14 and then Manager 2 for the remainder of the table. As it is fully populated I'm not sure what needs dragging down although it is pulling agent data as opposed to the average for each Manager.

Does this help at all or have I confused the issue further?

Kind regards

Andy

See attached file for the average data requirement.

Regards
Bosco

 

[vletm]

William Hulme
Next version with averages too:
You can make selection by activating 'Team Manager' or 'Advisor' "buttons".
You can select to see: max, average, median, min by activating that cell
(if bolded) then that row will be visible.
After wanted selection press 'Team Manager' or 'Advisor' again to activate those selections. (( Just test to activate those cells and see ... ))

If You need more those rows to see ... just let me know.
Ps. As You have noticed ... there are ... much much 'less' formulas.

 

[William Hulme]

vletm - thank you again for your time and effort in seeking to assist with my query. It is very much appreciated. The revised spreadsheet didn't quite address the average issue I was looking to resolve. The average calculated by the spreadsheet for Steve Wilson was for all of the agents reporting in to all of the 11 Team Managers reporting in to him. This then gave the final single figure of 480.9. What I was looking for was an average for each of the 11 Team Managers that would give 11 separate figures, that way each of the Team Managers can be easily compared. Perhaps I was not clear. It's sometimes difficult to explain requirements when I am too close to the issue.

The final spreadsheet was a little wide of the mark I was trying to hit but I am always interested in improving my excel knowledge and I will see if going there are any applications for it. Nonetheless, please accept my gratitude.

Bosco - I don't know what to say. You hit the nail on the head exactly. The output from the sheet you designed ticks all boxes. I still can't get my head around the aggregate function and will have to do a post mortem on your formula to see what it is actually calculating and how, same for the average formula you introduced today.

I've played around with the sheet you provided, adjusted it to accommodate month to date figures too and added the graphs I was needing.

This will perform the intended task 100%.

I would like to sincerely thank you for all of your time and effort both at this stage and the earlier part when you also assisted with the previous draft of the spreadsheet. If nothing else you've taught me that I have a lot to learn.

I've attached the final version so you can see where I took if after receiving it. I've sent it in to work where it can be used from tomorrow. Please also note the file name

Kind regards

Will

 

[vletm]

William Hulme - maybe I figure something now...
You would like to get only those averages - okay?
You will get only those to 'OM Dashboard'-sheet
and You can select 'Operations Manager' from 'OM Graphs'-sheet too.
Ps. ... and still without that one a... function nor many formulas.

 

[vletm]

William Hulme
Did You miss still one Graph?
You could compare 'Operations Managers' too in one sheet.
There are also possible to see those values; below of graphs.

 

[William Hulme]

Hi vletm,

Sorry for the late reply. I've been in work since early this morning and didn't have access to internet.

Re the graphs, I think they're all there. There's 6 graphs for OM's and corresponding ones at TM level. I did lose one at one point because I composed it in Excel 2016 and when I emailed it in to work, Excel 2010 did not recognise the graph and put a place holder there instead.

In theory it's an interesting thought to compare the OM's. In the structure, there are the OM's and then they all report in to 1 person. I don't think anybody has compared the OM's before... It might be the last thing I do before getting sacked for my cheek! That said, in all seriousness, it could be that the OM's don't want to be compared... That's not to say that the person at the top of the food chain doesn't want to compare them!

Thank you for suggesting this. I'll put it to the decision makers when I'm next in.

Kind regards

Will

 

[vletm]

William Hulme
That new compare sheet can hide if someone cannot stand truth.
It don't have 'any connection' with other sheets ...
or just calculate those values from WTD and MTD.
... or if it is too much then just delete it
Other sheets do not have any formulas to ... delete nor fix.
btw ... I just remember that some of Your 'original graphs data' had wrong range ... I had to edit all of those too.

 

[William Hulme]

You make a fair point... and thanks for editing the graph data ranges. I'm still getting to grips with many parts of excel. I used to use it years ago but just going through a period of refresher training and improvement, so any tips I pick up along the way are always welcome