1. ## Excel Formula

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. 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

 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. Try

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

4. can you put it into formula Bob?

5. That is a formula.

#### Posting Permissions

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