Thread: Point Out:If then formula

Hello, im working on a spreadsheet to help keep up with my bonuses. I need a formula that would calculate that if i am 90% to objective, i receive $35 per each percentage point over 90%. I also need to be able to input weather i am an "MVP" which gives an additional $360 at 90% and an additional $24 dollars per percentage point up to 120% or $1080.

thanks for the help!

Does the first bonus have a limit?

No there is no limit on the first one.

OK, say A1 is your % and B1 has MVP. You can use =MAX(A1-.9,0)*3500+IF(B1="MVP",Max(MIN(A1,1.2)-.9,0)*2400+360,0)

Thanks, i entered it in and the first bonus is working but the MVP bonus is not. I entered in numbers at 95%, and not being mvp i was at 175, but being mvp i was at $1255, when it should be 295. Thanks for your help!

At 95%, you should be at $655, shouldn't you?

oh yea your right i forgot about about the 360 you get at 90%

=IF($B26>=0.9,(($B26-0.9)*100)*35,0)+IF($B27="MVP",360+IF(($B26-0.9)*24*100>1080,1080,($B26-0.9)*100*24),0)

this one is working, thank you very much guys! i will prolly be back later to pick your brains again on the qualifiers, thanks again!

ok im needing to ad this to the formula as qualifers, u can use the one i posted above to have the correct cells, i thought i could just ad it in the front but its giving me an error.

=IF(AND(D26>0,F26>0,F21>0,D21>=3.5)

here is how i attempted to enter it

IF(AND(D26>0,F26>0,F21>0,D21>=3.5)($B26>=0.9,(($B26-0.9)*100)*35,0)+IF($B27="MVP",360+IF(($B26-0.9)*24*100>1080,1080,($B26-0.9)*100*24),0)

I tried the formula you posted there and when i enter it in it just enters it in as text not as a formula, also i tried manually entering in the ,IF i was missing and it does the same thing. here is what i entered.

IF(AND(D26>0,F26>0,F21>0,D21>=3.5),IF($B26>=0.9,($B26-0.9)*100*35,0)+IF($B27="MVP",360+IF(($B26-0.9)*24*100>1080,1080,($B26-0.9)*100*24),0),0)

Copied and pasted yours without the = in front of it. Sorry, assumed you knew all formulas require = in front. My other objection to the formula still holds. Essentially, as it is, the upper limit is 1.35, not 1.2.

ah yea i see that now sorry lol trying to work and do this at the same time

ok i just did the math and it seems to be working, 1.25 with mvp gives 2425, thats the 360 for 90% and 2065 for 35 points above 90%. without mvp it gives 1225.