I want to build a bonus worksheet

Asked By 10 points N/A Posted on -

Hello experts,

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.

SHARE
Answered By 0 points N/A #114717

I want to build a bonus worksheet

Hi Sabelle,

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:

=vlookup(lookup_value,table_array,col_index_num,[range_lookup])

where

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:

 Sales Bonus added to original sales ( examples only ) \$100-\$199 10% \$200-\$299 20% \$300-\$399 30%

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:

=(B11+(B11*VLOOKUP(B11,B4:C8,2,TRUE)))

Let's look at the vlookup part:

VLOOKUP(B11,B4:C8,2,TRUE)

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.