Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

IF Logical formula

Latest post Wed, Oct 16 2013 9:58 PM by Rick Williams. 1 replies.
  • Sat, Oct 5 2013 5:03 AM

    • Manjunath
    • Not Ranked
    • Joined on Sat, Oct 5 2013
    • Bangalore
    • Posts 1
    • Points 21

    IF Logical formula

    Hi All

    Can any one help me with IF Logical formula for below query.

    Scenario - In a call centre, need to allocate agents/ employee for multiple accounts based on their requirement, service level %, criticality and agents availability in the pool for allocation.

     

    Account 1

    Required Agents - 102

    Actual Agents    - 97

    Service level target - 95%

    Current Service level - 90 %

    Account criticality - High

     

    Account 2

    Required Agents - 33

    Actual Agents    - 32

    Service level target - 95 %

    Current Service level - 96 %

    Account Criticality - medium/low

     

    Availability in the pool for allocation - 4

     

    For allocating the agents for any particular accounts following things needs to be considered

    1. Actual versus Required agents (which ever the account has more difference of actual numbers than required should be given preference)

    2. Service level % of the account (whether it is meeting the target or missing target). The account not meeting the target should be be preference

    3. Criticality of the account (the account which is highly critical needs to be given prefence while allocating agents)

     

    Comparing to Account 1 and Account 2, one can generally say Account 1 should be given preference and all 4 available agnets in the pool should be allocated ot Account 1 because it has more difference in actual versus required agents, SL % target is missing and is high critical.

    Can this thought process ( Actual versus required agents, SL % and criticality of the account) put in as a logical conditions and in excel and automatically allocate the agents from the available pool ?

     

    Please help.. This will be of greater help.

     

    Thanks

    Manjunath

    email ID : cpmanju4u@gmail.com

     

    • Post Points: 21
  • Wed, Oct 16 2013 9:58 PM In reply to

    • Rick Williams
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Melbourne, Australia
    • Posts 162
    • Points 2,387

    What you haven't specified is which of the three tests take priority over the others.

    e.g.

    account one needs 2 more agents, but is low priority

    account two needs 1 more agent, but is high priority

     

    if you have one more agent in the pool, where should they go?

     

    If one of the conditions always overrides the others, then that can be done, or alternatively, a 'weighted' approach can be used - using what is called a multi criteria analysis (MCA) - you 'score' the need of each of the accounts, and assign the workers to the highest ones, recalculating after each assignment from the the pool.

     

     

    Rick Idea
    Melbourne, Australia

    Filed under:
    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.