Options

# Excel range fuckery [SOLVED]

Registered User regular
edited June 2012
So i'm trying to build a formula that will key off cells that define an upper and lower limit, for a commission calculation.

0-50,0000 2%
50,001-85,000 3%
85,001-100,000 8%

I'm trying to create a formula that will take a salesperson's commission number, and apply the correct percentage. I'm going to keep hunting around on google but i don't think i'm using the right terminology (I was trying range and array, but those are something else)

Any experts out there (this is probably super easy, i'm just dumb)?

Dr. Frenchenstein on

## Posts

• Options
on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
edited June 2012
Nested Ifs would do the job

=IF(C2<50001,.02,IF(C2<85001,.03,.08))

Deebaser on
• Options
on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
In english
If a Cell is less than 50,000 you want 2%, else IF a cell is less than 85,000 you return 3%, otherwise it's 8%

• Options
Registered User regular
Oh snap, lookup functions are the SHIT.

i was doing if's but i figured there had to be a better way.

• Options
Fuck Yes. That is an orderly anal warehouse. Registered User regular
VLookup and HLookup have an option to match as if it is matching a range. You would set up the table like:
0 | .02
50,001 | .03
85,001 | .08

So 20,000 would match .02. I don't know the exact syntax. It's been a while since I've been an excel monkey, but the help for those functions should get you the rest of the way.

• Options
Registered User regular
you actually don't need the V or the H

the formula i ended up using is (say those columns are a-c) =lookup(sales,A1:C3) and you get your result!!!

• Options
on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
edited June 2012
Use "vlookup". No one uses "lookup".
Just pointin' out best practices.

Deebaser on
• Options
Registered User regular
Deebaser wrote: »
Use "vlookup". No one uses "lookup".
Just pointin' out best practices.

Yeah, my version of Excel (2010) even points out that lookup is only provided for backward compatibility with older versions. Vlookup (and hlookup) give you a bit more control over the search via allowing you to select which column (or row) you return the result from and providing a boolean flag for either doing exact or nearest matches.

• Options
Registered User regular
vlookup looks for a direct match, right? I'm looking for a value within the range.

like if i did =vlookup(Sales,A1:C3,3) it would only return the percentage if the sales value matched a value in A, right?

• Options
on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
=vlookup(Sales,A1:C3,3,TRUE) will sort you out.

• Options
Registered User regular
edited June 2012
So it does! thank you sir, you are a scholar and a gentleman.

I've always used the FALSE designator, i've never even looked at what true did.

Mods, you can lock this

Dr. Frenchenstein on