Calculating Percentages

[Jimh64]

Hello! Thank you in advance for the help!

I am trying to use a calculation where the % used to calculate a number is based on the % entered.

For example:
If the % is below 5% then the multiplier would be zero.
If the % is 5.0% to 5.24% then multiply by 5%
If the % is 5.25% to 5.49% then multiply by 10%
If the % is 5.5% or higher then multiply by 15%

I need to enter in the % and then it will automatically multiply another cell by the correct %. This is for a commission situation.

For example, if sales were $1000 and the % entered in cell was 4.75% then no commission. If sales were $1000 and the % entered was 5.35% then the commission would be $1000 * 10% or $100

Any help is appreciated!

Thanks!

 

[Jimh64]

I thought this might help

So in this example the Delivered Number is entered at $75000. The PPP% is 4%, so the commission should be zero not $450.

Sales Goals Calc. v1.1.xlsx
	B	C	D
4	Current Actual		Comm.
5	Delivered $	$75,000.00	$4,500.00
6
7	PPP %	4.00%	$450.00
8
9	PPP $	$3,000.00
10
11
12
13	Avg. Sale	$1,200.00
14
15	# Sales	63
16
17
18		Total Comm.	$4,950.00
PPP Calculator

Cell Formulas
Range	Formula
D5	D5	=C5*6%
D7	D7	=C9*15%
C9	C9	=C5*C7
C15	C15	=C5/C13
D18	D18	=D5+D7

 

[KRice]

If you're using Excel 365, try this:

MrExcel_20220430.xlsx
	A	B	C
1	Sales	% Input	Commission
2	1000	5.35%	100
Sheet2

Cell Formulas
Range	Formula
C2	C2	=XLOOKUP(B2,{0,0.05,0.0525,0.055},{0,0.05,0.1,0.15},"error",-1,1)*A2

 

[Ezguy4u]

Jim because you can never have too many solutions. Here is mine.

Book1
	A	B	C
1	Amount	Percent	Commison
2	$ 1,000.00	5.35%	$100.00
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(B2>0.0549,A2*0.15,IF(B2>0.0524,A2*0.1,IF(B2>0.049,A2*0.05,A2*0)))

 

[Jimh64]

Thanks for the help! However, I forgot to tell you that there is a calculation in C9 and I think it messes up the formula.

The % in C7 is multiplied by the $'s in C5. Therefore $75,000 * 5.5% is $4,125.

5.5% means the $4,125 is multiplied by 15% to get the $618.75. The only numbers inputted are in yellow.

If it was 5.25% then it would be multiplied by 10% not 15% per the chart above.

Sorry to be a pain,,,, thanks!

Sales Goals Calc. v1.1.xlsx
	B	C	D
4	Current Actual		Comm.
5	Delivered $	$75,000.00	$4,500.00
6
7	PPP %	5.50%	$618.75
8
9	PPP $	$4,125.00
PPP Calculator

Cell Formulas
Range	Formula
D5	D5	=C5*6%
D7	D7	=XLOOKUP(C7,{0,0.05,0.0525,0.055},{0,0.05,0.1,0.15},"error",-1,1)*C9
C9	C9	=C5*C7

 

[KRice]

Is this what you mean?

MrExcel_20220430.xlsx
	B	C	D
4	Current Actual		Comm.
5	Delivered $	75000	4500
6
7	PPP %	0.055	618.75
8
9	PPP $	4125	<-- you don't need this for the calculation
Sheet4

Cell Formulas
Range	Formula
D5	D5	=C5*6%
D7	D7	=XLOOKUP(C7,{0,0.05,0.0525,0.055},{0,0.05,0.1,0.15},,-1,1)*C5*C7
C9	C9	=C5*C7

 

[Jimh64]

.

 

[Jimh64]

Is this what you mean?

MrExcel_20220430.xlsx
	B	C	D
4	Current Actual		Comm.
5	Delivered $	75000	4500
6
7	PPP %	0.055	618.75
8
9	PPP $	4125	<-- you don't need this for the calculation
Sheet4

Cell Formulas
Range	Formula
D5	D5	=C5*6%
D7	D7	=XLOOKUP(C7,{0,0.05,0.0525,0.055},{0,0.05,0.1,0.15},,-1,1)*C5*C7
C9	C9	=C5*C7

Thanks for the reply! Actually the number in C9 is used for the calculation of the commission.

I enter the Delivered Number ( C5) and the PPP% (C7). Depending on what the PPP% is is how you determine what to multiple the PPP$'s by in C9.
C5*C7 gives me C9.

If the % in C7 is below 5% then multiply C9 by zero
If the % in C7 is 5.0% to 5.24% then multiply C9 by 5%
If the % in C7 is 5.25% to 5.49% then multiply C9 by 10%
If the % is C7 is 5.5% or higher then multiply C9 by 15%

The result of that calculation would show up in D7

Hope this helps! I really appreciate the help from everyone!

 

[KRice]

Have you tried my suggestion in post #6? It does what you describe. My comment about not needing C9 for the calculation means that the value in C9 is not explicitly needed in the computation for commission shown in D7 because that same value is calculated within that formula as the product of C5 and C7. You can leave the value showing in C9 if you need to look at it, but the D7 commission formula does not refer to it.

 

[Jimh64]

Have you tried my suggestion in post #6? It does what you describe. My comment about not needing C9 for the calculation means that the value in C9 is not explicitly needed in the computation for commission shown in D7 because that same value is calculated within that formula as the product of C5 and C7. You can leave the value showing in C9 if you need to look at it, but the D7 commission formula does not refer to it.

Thank you! That does work. When you posted that originally, I thought you were asking a question about that field. It works like I had hoped it would.

I really appreciate you hanging in there with me to solve the problem. Have a nice day!!