I am trying to create an easy commission calculator. The data is tiered:
0-5 deals closed = 1.5%
6-11 = 2%
and so on.
I have the tiers stored in columns Y & Z, and what I want to do is use the actual # of deals closed (column A) to pull over the corresponding percent and multiply it by a number stored in column B and display the results in column C.
For example: I close 6 (A1) deals for 100k (B1). Column C should multiply 100k * 2%. I used to have a workbook that had a lookup function for this table but it is defunct and I can't seem to remember how to recreate it. Any help would be most appreciated.
Sincerely,
Jacob
jfarino@mindspring.com
Hi Jfarino,
You can use a LOOKUP function in C1, like this:
=B1*LOOKUP(A1,$Y$1:$Y$10,$Z$1:$Z$10)
Where column Y has the minimum for each range (e.g. 0, 6, 12, 18 ...) and column Z has the corresponding commission (e.g. 1, 1.015, 1.020, 1.025 ...)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks