Excel Formula

fairchance

New member
Joined
Jan 4, 2015
Messages
48
Reaction score
0
Points
0
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
Name12345
JohnUnqualified1211131110
Qualified4
5566
SallyUnqualified1311151313
Qualified6
3554
BenUnqualified1514121112
Qualified5
6455
TotalUnqualified4036403535
Qualified1514141615
RatiosQ to U (%)37.539354643
Commissions
Paid on Qualified leads only
Leads 1-5$10
Leads 6-10$15
Leads 11+$20





Mar
Week TotalAccumulative Monthly Total
12345
John40
50506565270270
Sally6530505040235235
Ben5065405050255255

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

Regards

Shehbaz H.
 
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
12345
JohnQualified45566
SallyQualified63554
BenQualified56455

The answer is:

John4050506565
Sally6530505040
Ben5065405050


What is the formula?

Regards

Shehbaz
 
Try

=SUMPRODUCT(--(C2>{0;5;10}), (C2-{0;5;10}), {10;5;5})
 
That is a formula.
 
Back
Top