Results 1 to 5 of 5

Thread: Excel Formula

  1. #1

    Excel Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear All

    Hi
    I need a spreadsheet that allows me to daily record Unqualified (U) & Qualified (Q) leads of 60 sales staff, give me daily and weekly totals for the team and individual salesperson, and calculate the Q to U percentage, then have excel calculate the daily accumulative amount to be paid (on a monthly pay cycle). I also need charts that show the daily Q amounts over time for each salesperson.

    The salespersons pay rate is thus:

    Leads 1-5 are paid at $10 each
    Leads 6-10 are paid at $15 each
    Leads 11+ are paid at $20 each

    For example, 7 qualified leads in a day are paid at (5x$10)+(2x$15)=$80, 13 qualified leads in a day are paid at (5x$10)+(5x$15)+(3x$20)=$185.

    EXAMPLE
    Mar
    Name 1 2 3 4 5
    John Unqualified 12 11 13 11 10
    Qualified 4
    5 5 6 6
    Sally Unqualified 13 11 15 13 13
    Qualified 6
    3 5 5 4
    Ben Unqualified 15 14 12 11 12
    Qualified 5
    6 4 5 5
    Total Unqualified 40 36 40 35 35
    Qualified 15 14 14 16 15
    Ratios Q to U (%) 37.5 39 35 46 43
    Commissions
    Paid on Qualified leads only
    Leads 1-5 $10
    Leads 6-10 $15
    Leads 11+ $20

    Mar Week Total Accumulative Monthly Total
    1 2 3 4 5
    John 40
    50 50 65 65 270 270
    Sally 65 30 50 50 40 235 235
    Ben 50 65 40 50 50 255 255

    I Want these cells in above to auto-populate when cells in bold have data added.

    Regards

    Shehbaz H.

  2. #2
    here is the simple requirements

    I need a formula to calculate the salespersons pay rate:

    Leads 1-5 are paid at $10 each
    Leads 6-10 are paid at $15 each
    Leads 11+ are paid at $20 each

    For example, 7 qualified leads in a day are paid at (5x$10)+(2x$15)=$80, 13 qualified leads in a day are paid at (5x$10)+(5x$15)+(3x$20)=$185.

    March
    1 2 3 4 5
    John Qualified 4 5 5 6 6
    Sally Qualified 6 3 5 5 4
    Ben Qualified 5 6 4 5 5

    The answer is:

    John 40 50 50 65 65
    Sally 65 30 50 50 40
    Ben 50 65 40 50 50


    What is the formula?

    Regards

    Shehbaz

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,834
    Articles
    0
    Excel Version
    O365
    Try

    =SUMPRODUCT(--(C2>{0;5;10}), (C2-{0;5;10}), {10;5;5})

  4. #4
    can you put it into formula Bob?

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,834
    Articles
    0
    Excel Version
    O365
    That is a formula.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •