# Percent over 100

I need a calculation for percent over \$100.  If \$175 then the amount over \$100 is \$75, thus 75%.  Full explanation here http://screencast.com/t/E3Xm3fkU.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultantCommented:
Set G2 to

=(F2-100)/100

And format as a percent.

Kevin
0
ConsultantCommented:
Set H2 to

=C2+0.3*(D2+E2)

Set I2 to

=0.6*F2

Set J2 to

=MAX(H2,I2)

Kevin
0
Author Commented:
Result:  http://screencast.com/t/ng77xKKWWE7

Thank you both for the assistance.
0
ConsultantCommented:
Change F2 to:

=MIN(F2,100+0.3*(100-D2+E2))

Kevin
0
Author Commented:
F2 matches the description.

Your new code on F2 gives a circular reference warning.

H and J aren't yet matching the description.

File attached
Commission-Schedule.xlsx
0
ConsultantCommented:
See attached. I placed the formula above in F2 as-is. No circular references.

Column J DOES match the description: "'If additional sales are over 90% of the first \$100 sold,
then you make 60% commission on all 'combined' sales for that day, whichever is highest."

The total sales for the day are 175. 60% of 175 is 105.

Kevin
Commission-Schedule.xlsx
0
Author Commented:
Column H says "At a MINIMUM, you make 100% of the first \$100 sold plus (+)
30% commission on all 'additional' sales for that day."

Since \$75 is the amount over \$100, then that column should represent \$100 + \$22.50 which is 30% of \$75.

Column J says "'If additional sales are over 90% of the first \$100 sold,
then you make 60% commission on all 'combined' sales for that day, whichever is highest."

Since additional sales are only 75% over 100% and not 90% or greater, then that value should be \$0
0
ConsultantCommented:
>Column H says "At a MINIMUM, you make 100% of the first \$100 sold plus (+)
30% commission on all 'additional' sales for that day." Since \$75 is the amount over \$100, then that column should represent \$100 + \$22.50 which is 30% of \$75.

And this is exactly what is calculated and what is displayed.

>Since additional sales are only 75% over 100% and not 90% or greater, then that value should be \$0

Then why, in your sample, do you show a value of 105 in column J? And why do you have column K which displays the maximum of H and J? With the maximum in column K you don't need to show zero in column J.

You never stated above that you wanted column J to be zero if the percentage over 100 is greater than 90%.

Also, even though you will be displaying zero in column J up to 190 in sales, you will be displaying a non-zero value in column J from 190 to about 230 in sales even though it is still less than the amount in column H.

That said, I made the changes per your last post in the attached file.

You might consider getting rid of columns H, I, and J and using a single formula to calculate the commission:

=MAX(MIN(F2,100+0.3*(100-D2+E2)),0.6*F2)

Kevin
Commission-Schedule.xlsx
0

Experts Exchange Solution brought to you by