BONUS Calculator

[nicodemus]

Hi everyone,

I wonder if you could help a fellow sould out here as I am completel
lost?

I am trying to create a workbook that generates a users bonus that i
given each quarter based upon them successfully achieving a give
target over a three month period.

I have two Worksheets.

The first has a column headed % to target that has fields ranging fro
100% to 125%.

This same sheet also has a column that is headed % Bonus that ha
fields ranging from 1 to 25%.

I also have another worksheet with four formatted areas :

The first allows a user to input their yearly salary.

The 2nd divides the yearly salary that has been input and generates th
quartely salary.

The 3rd box asks a user to enter their % to target.

The 4th box is where I am completely lost. I want the last box t
automatically multiply the users quarterly salary by the actual Bonus
that tally's with the % to target. The resulting figure would be th
users expected Bonus monetary pay!

CAN ANYONE HELP!

:confused

 

[Max]

In Sheet1
-----------
Assume you have the table in cols A and B,
headers in row1, data from row2 down
viz sample below:

%ToTarget...%Bonus
100%..............0%
105%..............5%
110%..............10%
115%..............15%
etc

(Note: %ToTarget col must be in ascending order)

In Sheet2
-----------

Put in D2:

=B2*OFFSET(Sheet1!$A$1,MATCH(C2,Sheet1!$A:$A,1)-1,1)

Copy D2 down

Col D will return the product of the User's quarterly salary in col B
and the %Bonus* corresponding to the %ToTarget in col C
*extracted from Sheet1