IF Function for numbers in a range w/ range of results

[Walnuts]

Newb here, I have found similar functions when searching here but nothing which includes the multiple functions I am looking for.
See illustration below.

Sales Goal $90,000 = Comission 1% of gross profit
Sales Goal Bonus $1500 @ $100,000

Sales Cost Of Goods Profit


The results in one cell I am looking for are if an Associate is <90,000=0$
>90,000=Profit*1%
>100,000=(Profit*1%)+Bonus$1500
The individual would earn no comission on sales under 90,000
The individual would earn standard comission on sales 90,000-99,999.99
The individual would earn standard comission + bonus on sales over 100,000

I would like the formulas to include fields rather than range of fixed numbers allowing me the flexibility to adjust sales goals, comission and bonus figures accordingly. I have spent several hours attempting this with minimal results.

 

[Paul Ked]

Hi and welcome to the forum.

What is the Profit? Can't calculate 1% of an unknown value!

 

[Walnuts]

Hi and welcome to the forum.

What is the Profit? Can't calculate 1% of an unknown value!

Sales $90,000
COGS $60,000
GP $30,000 GPM% 33.3%

Goal $90,000 Bonus 1% of GP = $300.00
Addl Bonus
$100,000 = $1500 + 300 = $1800.00

Formula results <90,000 = 0
>90,000<100,000 = 1% of GP
>100,000 = 1500 + 1% of GP

THANKS
PAUL

 

[C with no eyes]

A1 = Sales goal £90k
B1 = Associate's sales £60k
C1 = Sales bonus target £100k
D1 = Sales goal bonus £1500

<a1,0,if(b1>=IF(B1 < A1,0,IF(B1 > =C1,(B1-A1)/100+D1,IF(B1 > A1,(B1-A1)/100)))

Hope that helps</a1,0,if(b1>

 

[Walnuts]

A1 = Sales goal £90k
B1 = Associate's sales £60k
C1 = Sales bonus target £100k
D1 = Sales goal bonus £1500

<a1,0,if(b1>=IF(B1 < A1,0,IF(B1 > =C1,(B1-A1)/100+D1,IF(B1 > A1,(B1-A1)/100)))

Hope that helps</a1,0,if(b1>

Not working, perhaps I need to clarify.

A1 = Sales A2 = Results (Total Bonus & Comission combined for given sales associate per month)
B1 = Costs
C1 = A1-B1 (Profit)
D1 = C1/A1 (GPM%)
E1 = Sales Target $
F1 = Comission %
G1 = Bonus Target $
H1 = Bonus $

IF A1<E1 = 0
IF A1>E1 = F1*C1
IF A1>G1 = F1*C1+H1

Cells E1, F1, G1, H1 contain no formulas, asjustments to sales staff comission and bonus as well as targets will be needed.

I hope this clarifies better.

Thank You for your assistance

PAUL

 

[C with no eyes]

=if(a1 < =e1,0,if(a1 > =g1,(a1-b1)*f1+h1,if(a1 > e1,(a1-b1)*f1)))

 

[Paul Ked]

Name	Trgt	BnsLvl	Bns£	prft%	Act£	BnsErnd	Formula BnsErnd
John	£90,000	£100,000	£1,500	1.00%	£94,300	£943.00	=IF(F2<b2,0,(if(f2<c2,f2*e2,f2*e2+d2)))< td=""></b2,0,(if(f2<c2,f2*e2,f2*e2+d2)))<>
Jane	£90,000	£100,000	£1,500	1.00%	£107,800	£2,578.00	=IF(F3<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))< td=""></b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))<>

<tbody>
</tbody>

Formula bonus earned =IF(F3<B3,0,(IF(F3<C3,F3*E3,F3*E3+D3)))

 

[Paul Ked]

<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))[ code]
<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))
<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))"
<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))
<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))[ quote]

Strange things happening, can't post a formula!

= IF (F3 < B3 , 0 , ( IF (F3 < C3 ,F3 * E3 , F3 * E3 + D3 ) ) )

Take out the spaces!!</b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))[></b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))
</b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))"
</b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))
</b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))[>