Point Out:If then formula

[crazboy84]

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!

[darkyam]

Does the first bonus have a limit?

[crazboy84]

No there is no limit on the first one.

[darkyam]

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)

[Palmetto]

Assuming . . .

A1: holds "MVP" ( without quotes)
B1: percentage attained (i.e. 0.9, 0.92, etc.)

then perhaps in C1:

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

[crazboy84]

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!

[darkyam]

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

[crazboy84]

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

[crazboy84]

=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!

[crazboy84]

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)

[darkyam]

You need a comma between the end parenthesis of the AND statement and the rest of the formula and then start another IF statement. Like this:

Please Login or Register  to view this content.

Also, I don't think this formula is really correct. It doesn't take into account the 360 you'll be getting as a bonus when setting the upper limit. Put in you being an MVP with a 1.25 completion. This formula will return $120 more than you should be getting.

[crazboy84]

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)

[darkyam]

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.

[crazboy84]

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

[crazboy84]

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.

[darkyam]

It should be $2305. 1.25-.9=.35 .35*35=1225. 1225+360+(.3 (since 1.2 is the limit) *24)=2305.

[crazboy84]

ahh yea i forgot about the cap at 1.2, what can be entered to fix that? heres the formula now i moved some things on my spreadsheet around as it was somewhat cluttered.

=IF(AND(D19>0,D21>0,F21>0,B19>=3.5),IF(B17>=0.9,(B17-0.9)*100*35,0)+IF(B21="yes",360+IF((B17-0.9)*24*100>1080,1080,(B17-0.9)*100*24),0),0)

[darkyam]

To modify mine:

Please Login or Register  to view this content.

To modify Palmetto's:

Please Login or Register  to view this content.

[crazboy84]

your is working perfect! thanks very much i really appreciate it!

now i just gotta figure out where to get the sales to fill it out haha

[baxa]

I work on Excel 2003, with large data files, with VLOOKUP, HLOOKUP and other formulas, the files are becoming bigger but now it would not open more than 1 file at a time, also the Vlookup formulas take a lot of time and some times the computer gets stuck, making it difficult to save the data, but try to recover later.
The data is being processed for different years and even Pivot Tables are difficult to run with such huge data.
Can anyone suggest the best alternative in this problems ?

[darkyam]

Baxa, welcome to the forum. Please take a minute to read the rules, particularly rule #2, and then start your own thread. Thanks.

[crazboy84]

Hey guys, ran into another problem with my formulas, in the formula below, to recieve the additional payout from having yes in B21, you are not required to have anything in D19, D21, and F21, or B19, but you are still required to have those for the base payout, how can this be fixed?

=IF(AND(D19>0,D21>0,F21>0,B19>=3.5),MAX(B17-0.9,0)*3500+IF(B21="yes",MAX(MIN(B17,1.2)-0.9,0)*2400+360,0),0)

Also, in the below formula the way it is suppose to be is that in order to recieve the additional 50% for having yes in b22 you must surpass 300 in D21, how can that be fixed

=IF(D21>0,IF(D21>=300,D21*0.5,D21*0.25)+IF(B22="yes",(D21*0.5),0),0)

Thanks for all your help guys!

[darkyam]

=IF(AND(D19>0,D21>0,F21>0,B19>=3.5),MAX(B17-0.9,0)*3500,0)+IF(B21="yes",MAX(MIN(B17,1.2)-0.9,0)*2400+360,0).
=IF(D21>0,IF(D21>=300,D21*0.5,D21*0.25)+IF(AND(B22="yes",D21>300),(D21*0.5),0),0)

[crazboy84]

works perfect, your the man darkyam!

[crazboy84]

Just figured out there is a slight error in the formula, In order to recieve the $360 thats in the back of theformula B17 must be > than 90.0%, how can this be fixed? thanks!

=IF(AND(D19>0,D21>0,D17>0,B19>=3.5),MAX(B17-0.9,0)*3500,0)+IF(B21="yes",MAX(MIN(B17,1.2)-0.9,0)*2400+360,0)