# Software

## Question

Locked

### EXCEL FORMULA HELP

By ·
I need a formula for a tiered commission scale that looks like the below
70=\$700, 75=\$750, 80=\$880, 85=\$935, 90=\$990, 95=\$1045, 100=\$1200, 105=\$1380, 110=\$1580, 115=\$1840, 120=\$2100, 125=\$2188, 130=\$2275, 135=\$2363, 140=\$2450, 145=\$2538, 150=\$2625, 155=\$2713, 160=\$2800, 165=\$2888, 170=\$2975, 175=\$3063, 180=\$3150.

I would enter the numerical number in column B and in the cell directly beside it in column C the commission would be calculated based on the number you enter into column B. I have tried several "IF" formula's and can't find one that works and I am desperate at this point please help!!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

### VLOOKUP

by In reply to EXCEL FORMULA HELP

You want to use the VLOOKUP function. Start by creating two columns somewhere (I'm going to use columns A and B of Sheet2). In column A add your values (70, 75, 80 etc). In column B add the corresponding commission amount (700, 750, 880 etc). This is your lookup table.

Now go back to Sheet1. Enter your actual sales numbers into a column (I'm going with column E, just to be different). In column F insert a formula and select VLOOKUP. You'll need to enter four things:
1) The lookup value. Enter E1 here
2) The lookup table. Enter Sheet2!A1:B23 here.
3) The column offset in your lookup table. You want to select column 2 (I'll explain why below)
4) Range lookup. You'll probably want to set this to true.

What this does is:
* Takes the value (E1)
* Goes and looks for that value in the first column of your lookup table (ie. column A of sheet2)
* When it finds it, it then looks on the same row for the value to return. You chose 2, which means it will return the value from column B of sheet2.

So if you enter 80 into cell E1, the lookup will go and find 80 in Sheet2!A3. It will then return the value from column B - \$880

Now let's say you enter 79 into cell E1. If you set range lookup to false the lookup will fail because 79 does not exist in column A of Sheet2. If you set range lookup to false however it will go with the closest match it can find. The closest value in your lookup table is 75, so \$750 will be returned.

## Related Discussions

• 1

rebapelo ·

• 6

#### which can be the best and good cloud base software ?

tofiqueshaikh25 ·

• 0

Lcannon4 ·

• 2

#### Hello, is there a free server-based video chat ?

Nerdynerd247 ·

• 0

#### What program is used to process payroll?

pooja9990gupta ·