EXCEL 2010: Formula question
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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 = ?
For example, take (300) active tickets already in queue, then / that by Ticket completed that day = ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
Cheers,
Dave