Calculate Factors to Achieve Goal

datasoup

New Member
Joined
Aug 24, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm having a challenge in resolving an algebraic problem using excel and would really appreciate some help.

Scenario:
  • Call center has goal of answering 80% of calls offered within X time
  • Sum of calls currently answered at goal is 369
  • Forecast remaining calls in day is 1854
Objective:
  • How many calls need to be answered during the remaining intervals in order to reach the day goal?
 

Attachments

  • 0Xi9A.png
    0Xi9A.png
    35.6 KB · Views: 17

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Welcome to the MrExcel forum!

Assuming you update your columns as shown in your example, try:

Book1
ABCDEFGHI
1IntervalCalls OfferedCalls Answered at GoalForecast CallsMax Calls PossibleRatio# of calls neededCurrent AvgGoal Avg
26:00:00 AM6643660.651515 0.6473680.8
36:30:00 AM8556850.658824 
47:00:00 AM9059900.655556 Remaining avg needed
57:30:00 AM9361930.655914 0.846926
68:00:00 AM8757870.655172 
78:30:00 AM6943690.623188 
89:00:00 AM8050800.625 
99:30:00 AM113113 95.7
1010:00:00 AM117117 99.1
1110:30:00 AM133133 112.6
1211:00:00 AM139139 117.7
1311:30:00 AM111111 94.0
1412:00:00 PM9696 81.3
1512:30:00 PM9595 80.5
161:00:00 PM101101 85.5
171:30:00 PM115115 97.4
182:00:00 PM131131 110.9
192:30:00 PM136136 115.2
203:00:00 PM127127 107.6
213:30:00 PM115115 97.4
224:00:00 PM8787 73.7
234:30:00 PM8383 70.3
245:00:00 PM8484 71.1
255:30:00 PM7171 60.1
Sheet1
Cell Formulas
RangeFormula
F2:F25F2=IFERROR(C2/B2,"")
G2:G25G2=IF(F2<>"","",MAX(0,E2*$I$5))
H2H2=SUM(C2:C25)/SUM(B2:B25)
I5I5=(I2*SUM(E2:E25)-SUM(C2:C25))/(SUM(E2:E25)-SUM(B2:B25))
 
Upvote 0
Solution
Welcome to the MrExcel forum!

Assuming you update your columns as shown in your example, try:

Book1
ABCDEFGHI
1IntervalCalls OfferedCalls Answered at GoalForecast CallsMax Calls PossibleRatio# of calls neededCurrent AvgGoal Avg
26:00:00 AM6643660.651515 0.6473680.8
36:30:00 AM8556850.658824 
47:00:00 AM9059900.655556 Remaining avg needed
57:30:00 AM9361930.655914 0.846926
68:00:00 AM8757870.655172 
78:30:00 AM6943690.623188 
89:00:00 AM8050800.625 
99:30:00 AM113113 95.7
1010:00:00 AM117117 99.1
1110:30:00 AM133133 112.6
1211:00:00 AM139139 117.7
1311:30:00 AM111111 94.0
1412:00:00 PM9696 81.3
1512:30:00 PM9595 80.5
161:00:00 PM101101 85.5
171:30:00 PM115115 97.4
182:00:00 PM131131 110.9
192:30:00 PM136136 115.2
203:00:00 PM127127 107.6
213:30:00 PM115115 97.4
224:00:00 PM8787 73.7
234:30:00 PM8383 70.3
245:00:00 PM8484 71.1
255:30:00 PM7171 60.1
Sheet1
Cell Formulas
RangeFormula
F2:F25F2=IFERROR(C2/B2,"")
G2:G25G2=IF(F2<>"","",MAX(0,E2*$I$5))
H2H2=SUM(C2:C25)/SUM(B2:B25)
I5I5=(I2*SUM(E2:E25)-SUM(C2:C25))/(SUM(E2:E25)-SUM(B2:B25))
Thank you kindly for the quick answer!
 
Upvote 0

Forum statistics

Threads
1,223,705
Messages
6,173,986
Members
452,541
Latest member
haasro02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top