How can I calculate commissions that are not percentage based?

[Sean]

I am trying to create a formula to calculate my commissions, but my
commissions aren't percentage based. The difference between my bill rate and
pay rate is the spread. If my spread is between $20 and $30, for example, I
get $500. If the spread is between $30.01 and $40, I get $1000, etc. So, the
higher the spread, the higher my commissions are. I have a spread sheet that
calculates my spread, but I'd like to track commissions also. Any ideas out
there?

 

[gjcase]

I'd create a commission table as a couple columns, Spread and
Commission. Then next to your Spread calculation, you can place a
Vlookup which references the spread number and the commission table.
The table could be on a different worksheet if necessary.

See VLOOKUP under help.

---GJC

 

[Sean]

Thanks for the help. I've never created a commission table. Do you have a
simple explanation on how to do this?

 

[gjcase]

By commission table, I just meant a table of the spreads versus
comissions, i.e., put the spreads in col A and the corresponding
commissions in Col B.

Example: ("Spread" is in A1)

Spread Com
0 100
20 500
30.01 1000
50.1 2000
100.1 5000

If your spread is in cell D1, then in E1 put =VLOOKUP(D2,A2:B6,2,TRUE)


Spread = 35 ==> 1000

HTH

---GJC