anyone help on this one?

[fivermsg]

at 90% of company goal i get paid 1.84$, for every full 1% increase up
to 99% i get 9 cents more ... any formula out there that could solve
this for me?

ex- (90%- 1.84) , (91%- 1.93) , (92%- 2.02) and so on up to 99%..

I got this formula earlier, IF(E10<0.9,0,1.84+(E10-0.9)*9) but it works
as a gradual increase as oppose to just a flat 9 cents every 1 percent

 

[Guest]

Try:

=IF(E10<0.9,0,1.84+INT(MOD(E10*10,1)*10)*0.09)

 

[fivermsg]

PERFECT!! thanks

 

[fivermsg]

1 more thing... i am now trying to make that form work with the same
formula on a different scale and i can't get the if functions to work
like i have in the past..

ex IF(E10<0.9,0,1.84+INT(MOD(E10*10,1)*10)*0.09)
IF(E10<1,0,2.76+INT(MOD(E10*10,1)*10)*0.11)

I would like these to formulas to combine and of course many others,
but i would like the first if formulas to be omitted when the
percentage for 2nd if formula comes into play...

 

[JE McGimpsey]

one way:

=IF(E10>=0.9, 1.84 + MIN(E10 - 90%, 9%) * 0.09, 0)

Alternatively:

=(E10>=0.9)*(1.84 + MIN(E10 - 90%, 9%) * 0.09)

Not sure why you stopped at 99% rather than 100% (or greater)...

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/variablerate.html

For instance:

=(SUMPRODUCT(--(E10-{0.9,0.99}>0),--(E10-{0.9,0.99}),{9,2}) +1.84) *
(E10>=90%)

 

[fivermsg]

I stopped at 99% cause at 100% it changes to an increase of 11 cents..
and again at 110% it changes to and increase of 25 cents, at 125% it
changes to an increase of 30 cents, at 135% it changes to 60 cents and
from then on i am looking at add in 60cents only for every 5% increase
as oppose to every percent. so at 135% till infinity at every 5%
increase there would be a increase of 60cents forever..

think you could make one for that.

 

[JE McGimpsey]

Piece of cake:

=IF(E10<90%, 0, IF(E10>=135%, 11.1 + FLOOR(E10-134.999999%,5%)*12,
1.84+SUMPRODUCT(--(E10-{0.9,0.99,1.09,1.24}>0),
--(E10-{0.9,0.99,1.09,1.24}), {9,2,14,5})))

 

[fivermsg]

K, that one does work but the only thing is that it add totals on
fractions of %'s.... i only want it to change on full percentages..
could you through and =int, in there somewhere??

 

[fivermsg]

and that formula is inaccurate from the 90%-99% range

 

[JE McGimpsey]

It would be easiest to round E10 to a full percentage:

E10: =ROUND(<current formula>, 2)

but you could substitute ROUND(E10,2) for any reference to E10 in the
formula I gave you.

 

[JE McGimpsey]

It gives exactly the values you specified for 90%, 91% and 92% in E10.

What values are wrong, and what should they be?