N/APosted on - 01/16/2012
I want to build a bonus worksheet which will linked to an invoice spreadsheet via use of macro.
It is based upon invoice sales , the bonus, I would like to have on a sliding scale, but I don’t know what formula to use?
As an example, I want to do is, if sales value is in cell A1=$100-$200, then cell C1 (which will calculate the bonus that staff will get) must be the figure shown in A1*A%. I A1 = the sum of $300-$400, this should be calculated at slightly higher percentage.
Please reply me if you need more explanation.
I want to build a bonus worksheet
We can calculate the bonus by using the VLOOKUP function found in Excel.
The way vlookup works is that it looks for a certain value from a list and return the value you need. Its syntax looks like this:
lookup value – The value that you will use,
table_array – The table you’re using
index_num – The column where the value you need is located
range_lookup-either TRUE to get exact value or FALSE to get approximate
For your case, you need to calculate bonus based on a range of sales.
I’ve created a table that illustrates this:
Bonus added to original sales ( examples only )
And so forth. The first thing you need to do in excel is to create a table where you're going to lookup your values, like so:
Cell B10 is where you're going to enter the sales figure value while cell C11 will calculate the bonus. The formula for cell C11 is:
Let's look at the vlookup part:
What this does is that you're going to use the value in B11, lookup it up using the table from B4:C8, use the value in the 2nd column of the table and TRUE means it is not looking for an exact match but an approximate which is useful if we're going to look for a value within a certain range.
The vlookup function in the formula gets what percentage we are going to use depending on the value you put into B11.
So, say you put 199 in B11, vlookup will look for the approximate of 199, pick what percentage to use and then using that percentage, calculate the bonus accordingly.
For more info on vlookup, please click on the link below: