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

Walnuts

New Member
Joined
Jan 25, 2018
Messages
3
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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>
 
Last edited:
Upvote 0
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
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Trgt[/TD]
[TD]BnsLvl[/TD]
[TD]Bns£[/TD]
[TD]prft%[/TD]
[TD]Act£[/TD]
[TD]BnsErnd[/TD]
[TD]Formula BnsErnd[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]£90,000[/TD]
[TD]£100,000[/TD]
[TD]£1,500[/TD]
[TD]1.00%[/TD]
[TD]£94,300[/TD]
[TD]£943.00[/TD]
[TD]=IF(F2<b2,0,(if(f2<c2,f2*e2,f2*e2+d2)))< td=""></b2,0,(if(f2<c2,f2*e2,f2*e2+d2)))<>[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]£90,000[/TD]
[TD]£100,000[/TD]
[TD]£1,500[/TD]
[TD]1.00%[/TD]
[TD]£107,800[/TD]
[TD]£2,578.00[/TD]
[TD]=IF(F3<b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))< td=""></b3,0,(if(f3<c3,f3*e3,f3*e3+d3)))<>[/TD]
[/TR]
</tbody>[/TABLE]

Formula bonus earned =IF(F3<B3,0,(IF(F3<C3,F3*E3,F3*E3+D3)))
 
Last edited:
Upvote 0
<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)))[>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,324
Latest member
stuart1980

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top