Excel Formula

**fairchance** · 2015-02-23, 07:14 PM

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.

**fairchance** · 2015-02-24, 12:02 PM

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