Here's one possible method, using VBA to loop through each row, then trying to use Goal Seek to set the number of agents.
Note that your data is not solvable to 80% in all cases (in these cases, the original formula will remain in Column F).
Sub DetermineAgentLevels()
Dim lRow As Long
Dim iFail As Integer
Dim sFormula As String
On Error GoTo Terminate
Debug.Print Now, "START"
Application.ScreenUpdating = False
With Worksheets("Staffing")
For lRow = 7 To 17
Application.StatusBar = "Processing Row " & lRow
With .Cells(lRow, "F")
sFormula = .Formula
.Value = .Value
End With
.Cells(lRow, "N").GoalSeek Goal:=80, ChangingCell:=.Cells(lRow, "F")
If Not Round(.Cells(lRow, "N"), 0) = 80 Then
Debug.Print "Unable to find solution for Row " & lRow
.Cells(lRow, "F").Formula = sFormula
iFail = iFail + 1
End If
Next lRow
End With
MsgBox "All rows processed - unable to solve " & iFail & " rows"
Terminate:
If Err Then
Debug.Print "ERROR", Err.Number, Err.Description
Err.Clear
End If
With Application
.StatusBar = False
.ScreenUpdating = True
End With
End Sub
Bookmarks