How to Calculate Area/value between two lines on Chart?

[Dhirendra]

Happy New Year and Thank you Chandoo for running a wonderful website on EXCEL and VBA. I have learned VBA recently and was asked to work on a small project on staffing model in 24x7 hour environment. I come up with a small VBA Code and made a model which is actually calculating the different between Ticket Volume % and Staff Availability %. I do not know what to call this difference as sometimes it is going more than 100%. Also I have come up with a trick to fill the gap between two lines on Chart.

My question:

Is there any other way possible to fill the gap between two lines.

Is there any other calculating method to showcase the best possible staffing to cover all workload.


I am not able to send you my Excel Sheet......

 

[Faseeh]

Hi Dhirendra,


Welcome to the forums! you can randomize your data and upload it and/or send it to faseeh10@hotmail.com


It is practically easier to calculate things from sheet/cell rather then charts, because charts reflect what you have put in cells so use cell to calculate this difference and show it on graph.


Faseeh

 

[NARAYANK991]

Hi Dhirendra ,


My comments are not pertaining to Excel , but to the model itself.


1. Is your staff availability constant i.e. the same throughout , or does it vary ? If it varies , on what does the variation depend i.e. is it seasonal , or is it less on weekends , or what ?


2. More than the gap between Ticket Volume and Staff Availability , I think the Key Performance Indicators will be two : what is the response time relation between Ticket Volume and Staff Availability i.e. is the response time the same till a particular level of Ticket Volume , and then it starts to increase rapidly or does the response time show a random trend ?


Another indicator is the percentage fulfillment over a long enough period , say 6 months to a year ? i.e. you must be having some benchmark for how long it takes for a ticket to be closed , or how many tickets an individual can handle on average ,...


In general , the Ticket Volume will fluctuate , and there will be some periods when the Staff Availability is more than capable of handling the Ticket Volume ; there will be certain peak periods when the Ticket Volume cannot be handled by the Staff Availability. You need to see what is the Ticket Volume multiplied by the number of days , so that there some weightage to the number of days when Ticket Volume was handled well , compared to the number of days when it was not.


We can discuss at greater length , but I think this is enough to start with.


Narayan

 

[Dhirendra]

@Faseeh: I have sent you the file

@Narayan:

1. Staff Availability is varying in nature.

2. I have different AHT for different tkt, but currently assuming AHT is constant for every ticket. I believe it would not be difficult to integrate in future.

3. My challenge is to create a EXCEL based tool to showcase if staff availability is correct according to ticket volume.


I want to send you my file, if you want to investigate further.

 

[NARAYANK991]

Hi Dhirendra ,


No problem ; you can send it to me now , or you can wait for Faseeh to respond.


My email address is narayank1026(at)gmail(dot)com


Narayan

 

[Dhirendra]

Hi Narayank,


I have send you the file, please look into it.


Dhirendra

 

[Dhirendra]

Hi Narayan, I have already share my EXCEL File with you, please forget my second question, please advise me on one first question:


#1. Is there any other way possible to fill the gap between two lines.


I have already gone through,

http://davidmerlemontgomery.blogspot.in/2009/09/two-color-xy-area-combo-chart.html#more

 

[NARAYANK991]

Hi Dhirendra ,


Thanks for posting the link ; are you now saying that what is shown in the link is not what you want ? Do you want something different ? If so , what exactly do you want ? Can you please explain ?


Narayan