formula help, calculating variable commission on scaling targets

[tray2014]

Hello Mr Excel Forum
What is a formula for the following hypothetical scenario:
I get paid a fee for signing up users to a website. The base fee ($0.5)
For the first 1 million users who verify, I receive an additional fee ($0.5)
For 1 million to 2 million users who verify, my additional fee increases ($0.75)
For 2 million or more who verify, my additional fee increases again ($1.00)

Example: Let's say I sign up 4.5 million users and verify 3.5 million users
Sign ups result in a fee of $2,250,000 (4.5 million users signed up)
Up to the first million verifications result in a fee of $500,000 (1 million multiplied by $0.5)
The 1 million to 2 million verifications result in a fee of $750,000 (1 million multiplied by $0.75)
And the 1.5 million after the 2 million result in a fee of $1,500,000 (1.5 million multiplied by $1)
total fees to be received for 4.5 million sign-ups with 3.5 million verifications is: $3,250,000

A1 of the spreadsheet would be the total sign-ups.
A2 of the spreadsheet would be the total verified sign-ups.

Thank you in advance for all your help. I've been struggling with this for two days.

 

[Dave Patton]

T202004a.xlsm
	A	B	C
1	4,500,000.00	Sign-ups
2	3,500,000.00	Verified
3	5,000,000.00	Amount	5,000,000.00
4
5
6	Units	Rate
7
8	0	0.50
9	1,000,000	0.75
10	2,000,000	1.00
1c

Cell Formulas
Range	Formula
A3	A3	=A1*0.5+SUMPRODUCT(--(A2>{0;1000000;2000000}),--(A2-{0;1000000;2000000}),{0.5;0.25;0.25})
C3	C3	=A1*0.5+SUMPRODUCT(--(A2>A8:A10),--(A2-A8:A10),B8:B10-B7:B9)

The formula is shown with and without the Table for rates.

 

[Dave Patton]

I added an arithmetic version.

T202004a.xlsm
	A	B	C	D
1	4,500,000.00	Sign-ups
2	3,500,000.00	Verified
3	5,000,000.00	Amount	5,000,000.00	5,000,000.00
4
1c

Cell Formulas
Range	Formula
A3	A3	=A1*0.5+SUMPRODUCT(--(A2>{0;1000000;2000000}),(A2-{0;1000000;2000000}),{0.5;0.25;0.25})
C3	C3	=A1*0.5+SUMPRODUCT(--(A2>A8:A10),(A2-A8:A10),B8:B10-B7:B9)
D3	D3	=A1*0.5+(A2>0)*A2*0.5+(A2>1000000)*(A2-1000000)*0.25+(A2>2000000)*(A2-2000000)*0.25

 

[tray2014]

Thank you Dave,
I will give this a try

 

[tray2014]

Good morning Dave
Would you be kind enough to explain how the formula works?
=A1*0.5+SUMPRODUCT(--(A2>{0;1000000;2000000}),(A2-{0;1000000;2000000}),{0.5;0.25;0.25})

For example ... A1*0.5 the 0.5 value is the 50 cents?
next ... A2>{0; ... what does the 0 value represent? and why semi-colons?
last ... {0.5;0.25;0.25} this looks like an array, but I'm confused as to why two 0.25 values?
and so forth with the other formulas for the remaining cells

Dave, sorry for the questions. But I do see a lot of questions regarding similar problems and can't help but think that the extra clarity will help not just myself but others like me who are confused.

Thank you in advance

 

[Dave Patton]

For example ... A1*0.5 the 0.5 value is the 50 cents? yes

Please look at the formula with the rate table (post #2 shows the Table)
A1*0.5+SUMPRODUCT(--(A2>A8:A10),(A2-A8:A10),B8:B10-B7:B9)

You can use Excel's Formula Evaluate to step through the formula.
The part of the formula B8:B10-B7:B9 calculates the rate difference.

The SumProduct formula is consistent with the arithmetic solution that I posted.

When you review the formula, you will see the the table yields the same arrays that
I used in the formula that asked about.

If you require additional explanation, please advise.

Dave

 

[tray2014]

Dave - awesome and thank you. This is very helpful.

 

[CA_Punit]

Clarification:
1. A1*0.5 the 0.5 value is the 50 cents? ----Correct. (On signing up 0.5 cent is received)
2. =+SUMPRODUCT(--(A2>A8:A10),--(A2-A8:A10),B8:B10-B7:B9)

Dave has put one Condition in --(A2>A8:A10) i.e. whether 3500000 is greater than the Range A8:A10. So it will result in {1,1,1} which means true, true, true.
2. No the above condition is multiplied with (A2-A8:A10) Which means 3500000 is deducted from each criteria {3500000-0=3500000; 3500000-1000000=2500000; 35000000-2000000=1500000}
3. B8:B10-B7:B9)--- This results in incremental rate for each range calculated as per step 2.

Hope you find helpful

 

[tray2014]

Thank you. Am going through it now.

 

[AhoyNC]

This link should help explain formula.

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates