help with conditional formula?

[thedocs]

Hello! I'm new to Excel and trying to come up with a formula the hel
us in our new company. www.TheDocsOnline.com


Can someone help me with a formula or point me in the right direction?
I need a formula the will show our tiered commission as follows.

Column 1 Enter any value X
Column 2 Shows 35% of the first $300
Column 3 Shows 28% of the next $300.01 to $700
Column 4 Shows 19% of 700.01 and up

I need to be able to enter an amount in column 1 and see the % in th
next 3 columns.

I think the formulas should be

C2 - if X is = to or less than 300 than (X*.35) if X is great tha
300 then c2=35

C3 - if X is greater than 300 but less than 700.01 then X-300*19 ?????
I need this formula to show me 28% of the amount between $300 to $700

C3 - if x is greater than 700 then X-700*.19

I hope this makes sense and that someone can give me assistance.

Thanks, Thomas E Wilkerso

 

[Kevin H. Stecyk]

TheDocs wrote...

Hello! I'm new to Excel and trying to come up with a formula the help
us in our new company. www.TheDocsOnline.com


Can someone help me with a formula or point me in the right direction?
I need a formula the will show our tiered commission as follows.

Column 1 Enter any value X
Column 2 Shows 35% of the first $300
Column 3 Shows 28% of the next $300.01 to $700
Column 4 Shows 19% of 700.01 and up

I need to be able to enter an amount in column 1 and see the % in the
next 3 columns.

I think the formulas should be

C2 - if X is = to or less than 300 than (X*.35) if X is great than
300 then c2=35

C3 - if X is greater than 300 but less than 700.01 then X-300*19 ?????
I need this formula to show me 28% of the amount between $300 to $700

C3 - if x is greater than 700 then X-700*.19

I hope this makes sense and that someone can give me assistance.

Thanks, Thomas E Wilkerson

Hi,

Have a look here: http://www.mcgimpsey.com/excel/variablerate.html

I have bookmarked that location as the solution is quite clever. You might
want to provide a graph of your commissions?

Hope it helps.

Regards,
Kevin

 

[Ron Rosenfeld]

Hello! I'm new to Excel and trying to come up with a formula the help
us in our new company. www.TheDocsOnline.com


Can someone help me with a formula or point me in the right direction?
I need a formula the will show our tiered commission as follows.

Column 1 Enter any value X
Column 2 Shows 35% of the first $300
Column 3 Shows 28% of the next $300.01 to $700
Column 4 Shows 19% of 700.01 and up

I need to be able to enter an amount in column 1 and see the % in the
next 3 columns.

I think the formulas should be

C2 - if X is = to or less than 300 than (X*.35) if X is great than
300 then c2=35

Shouldn't C2 = 35% of 300 if X is greater than 300? That would be 105, not 35.

C3 - if X is greater than 300 but less than 700.01 then X-300*19 ?????
I need this formula to show me 28% of the amount between $300 to $700

C3 - if x is greater than 700 then X-700*.19

I hope this makes sense and that someone can give me assistance.

Thanks, Thomas E Wilkerson

Since it seems as if you want to see the individual tiers, this set of formulas
should work:

C2: =35%*MIN(A2,300)

C3: =MAX(0,28%*MIN(400,A2-300))

C4: =MAX(0,19%*(A2-700))


If you have more flexible tiers, or more values, and you don't need to see the
individual breakdowns, a lookup table might be more efficient.


--ron

 

[thedocs]

I think I got the first one (35% of the first $300)

=(C5<=300)*(C5*0.35)+IF(C5>300,35)

The last one (17% of $700 & up)

=(C5>300.01)*((C5-700)*19%)



Both seem to work. Please let me know if theres a better wa

 

[Cutter]

This seems to work:

=IF(F19<300.01,F19*0.35,105+IF(F19>700,112+(F19-700)*0.19,IF(F19<700.01,(F19-300)*0.28,196)))

To allow for no entry in the cell use this:

=IF(F19=0,"",IF(F19<300.01,F19*0.35,105+IF(F19>700,112+(F19-700)*0.19,IF(F19<700.01,(F19-300)*0.28,196)))

 

[thedocs]

Thanks! Just what I need. Now I'll try to figure out why it works

 

[Cutter]

I used constants in the formula based on the percentages you gave. I
the percentages or set values change then the formula won't work. Yo
might want to put your values and percentages in separate cells an
have the formula refer to them. That way you can change your values o
percentages and still have a working formula