Formula help for calculating targets

[emmalouiseee]

Hey!

I am having a hard time getting a formula in 1 cell that will do what I need. Maybe it's really easy and I am just having a moment or maybe it's just not possible.

Scenario:

Target between 0-10000 needs to be multiplied by 0.85 THEN anything after this point needs to be multiplied by 0.30

I understand this may not be possible in 1 cell so I have tried splitting it into two but then I am getting another issue where if the value is 15000 I need the first equation to give me 0-10000 at 0.85 and then the second cell to give me 10001-15000 at 0.30 but I tried an IF statement with no luck.

[TABLE="width: 530"]
<tbody>[TR]
[TD="colspan: 2, align: center"]Option One[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$ Target[/TD]
[TD="align: center"]Comm[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15000[/TD]
[TD="align: center"]4499.7[/TD]
[TD="align: center"]<<<<[/TD]
[TD]1 Formula to figure out Comms


[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 594"]
<tbody>[TR]
[TD="colspan: 3"]Option Two [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]$ Target[/TD]
[TD="align: center"]0-10000[/TD]
[TD="align: center"]10001+[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8000[/TD]
[TD="align: center"]6800[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15000[/TD]
[TD="align: center"]3000[/TD]
[TD="align: center"]1499.7[/TD]
[TD="align: center"]<<<<[/TD]
[TD]Split into two formulas[/TD]
[/TR]
</tbody>[/TABLE]




Thanks in advance!

 

[jtakw]

Hi,

Are those typos or I'm completely misunderstanding your question?

Why does 15000 Only get 4499.7, but 8000 gets 6800 ??

 

[joeu2004]

I'm confused. Your title says that to "calculate targets". But your example suggests that you are given the target, and you want to calculate something (commission; tax?) based on a tiered rate structure.

Also, your calculation for 15000 is incorrect. For the first 10000, you multiplied by 0.30 instead 0.85. For the amount over 10000 (5000), you calculated by 0.29994 (approx) instead of 0.30.

The correct calculations are:

Book1
	A	B	C	D	E
1	Amt	One Formula	<=10000 @85%	>10000 @30%	Total
2	8000	6800	6800	0	6800
3	15000	10000	8500	1500	10000
Sheet1

The formula in B2 is:

=MIN(8500,A2*0.85) + MAX(0,(A2-10000)*0.3)

The formulas in C2:E2 are:

C2: =MIN(8500,A2*0.85)
D2: =MAX(0,(A2-10000)*0.3)
E2: =SUM(C2:D2)

 

[emmalouiseee]

Hey!

I am having a hard time getting a formula in 1 cell that will do what I need. Maybe it's really easy and I am just having a moment or maybe it's just not possible.

Scenario:

Target between 0-10000 needs to be multiplied by 0.85 THEN anything after this point needs to be multiplied by 0.30

I understand this may not be possible in 1 cell so I have tried splitting it into two but then I am getting another issue where if the value is 15000 I need the first equation to give me 0-10000 at 0.85 and then the second cell to give me 10001-15000 at 0.30 but I tried an IF statement with no luck.

[TABLE="width: 530"]
<tbody>[TR]
[TD="colspan: 2, align: center"]Option One[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$ Target[/TD]
[TD="align: center"]Comm[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15000[/TD]
[TD="align: center"]4499.7[/TD]
[TD="align: center"]<<<<[/TD]
[TD]1 Formula to figure out Comms

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 594"]
<tbody>[TR]
[TD="colspan: 3"]Option Two [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]$ Target[/TD]
[TD="align: center"]0-10000[/TD]
[TD="align: center"]10001+[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8000[/TD]
[TD="align: center"]6800[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15000[/TD]
[TD="align: center"]3000[/TD]
[TD="align: center"]1499.7[/TD]
[TD="align: center"]<<<<[/TD]
[TD]Split into two formulas[/TD]
[/TR]
</tbody>[/TABLE]




Thanks in advance!

Sorry that was so confusing! it was how it was explained to me - doing for a colleague at work.

I have used this formula and it works perfectly - thank you so much!! saved a lot of time

 

[ncrcnbl]

If I understand the question, I think another way to do this would be with nested IF functions.