I want to build a bonus worksheet

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.

For more info on vlookup, please click on the link below:

Cheers,

Michael