Solved

EXCEL 2010:  Formula question

Posted on 2012-04-02
6
334 Views
Last Modified: 2012-04-09
Hello,

    I have a spreadsheet that I use to track inbound calls.  I already have the formula that determines whether or not I'm understaff or over-staffed for that day simply by dividing =D25/D5 cells.  If it it's under 100% then technically I'm understaffed for that day, if it's over 100% then I'm over-staffed for that day.  This is noted on the far right of my screen shot labeled "Complete to Inbound %."

    With that said, what I'm trying to pull now, is a headway percentage for the day.  For example:  How much gain did we make based off of the total of "Total New Tickets" vs. "Total Completed Tickets" received and complete?  You'll see my example in the screenshot attached. (located to the far right) and is labled "Actual Gain %".   Wonder if someone might be able to help me figure out the formula or workaround to this?

     Your help is greatly appreciated!
test.jpg
0
Comment
Question by:itsmevic
6 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37799071
Please upload a sample workbook to make this a bit easier in the translation.

Cheers,

Dave
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 37799173
It should simply be

Total completed tickets / Total new tickets

and would show up something like

=B20/B5

depending on the address of each of the cells.

You would have to format it as a %age in number format
0
 
LVL 80

Expert Comment

by:byundt
ID: 37799229
I think the metric of interest might be whether the backlog is increasing or reducing in percentage terms. So 0% would mean no change in backlog, negative numbers would be a percentage reduction in backlog, and positive numbers would be a percentage increase in backlog

From the jpg screenshot, I assume that D13 is the backlog at the end of today, while D14 is the backlog at the end of yesterday.  The percentage change in backlog can be calculated as (Yesterday's backlog - Today's backlog)/(Yesterday's backlog). This expression can be simplified to:
=D13/D14 - 1                 answer expressed as a percentage

If there is a possibility that D14 might someday be 0, then you could handle that possibility with:
=IFERROR(D13/D14-1,"Not meaningful")             answer expressed as a percentage
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:itsmevic
ID: 37804233
If I were to add the total number of tickets in the que presently (300) to this would that help in determining the "actual gain %" for that day?   If that was the case then would I only need to divide the "total tickets completed" into the "total number of tickets in the que" to get a percentage of a gain for that day?  

For example, take (300) active tickets already in queue, then / that by Ticket completed that day =  ?
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 37804323
If you divided the "total number of tickets completed in the day" by "the total number of tickets in the queue" you would get the percentage of tickets that are satisfied today. And if you divide the total number in the queue by the total number completed in the day, you would get the number of days it might take to exhaust the queue (assuming no more tickets come in). Both of those are interesting ratios, but neither of them includes the concept of "gain."

When you say "gain," you are really talking about a slope or rate of change. That's why I suggested dividing today's backlog by yesterday's backlog--to give the percentage gain (or loss) in backlog per day.

Are you in the position of trying to interpret "boss speak" so you can produce the desired statistics? If so, perhaps the exact words used to describe the mission might suggest a tactic.

Or are you on your own, trying to figure out useful performance metrics? In this latter case, I would find it interesting to see the number of days of backlog, the number of hours a ticket remains active, the percentage change in backlog (previously suggested in this thread), and the coverage ratio (tickets closed/new tickets from your previous question).

To determine the number of hours a ticket remains active, you would study the tickets that got closed each day. In that study, you would add up the number of hours that each ticket remains open, then divide by the number of tickets closed that day. For tickets that remain open for more than one day, you will need to decide how many hours to add for each additional day the ticket is open--this could be the number of hours of help desk operation, for example. Whether you have the data to support this calculation, I cannot say. The screen shot certainly didn't show enough, but the raw data might support such a calculation.
0
 

Author Closing Comment

by:itsmevic
ID: 37825938
Thank you!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now