How to write an IF formula using a range?

**Glh222** · 01-11-2013, 07:03 PM

Hi guys, I was wondering if it was possible to write an IF formula using a range?

Meaning, I am attempting to calculate a commision scale for my employees using a tier bump. For instance....if they sell between 3-6 units, I want the total commision to be 13%. If they sell 7-10 units, I want the commision to be 14%. If 10+ I want 15%. Is the IF formula the correct way to go? From what I can tell, its limited to true or false, which wont help me....any ideas? thanks again!

**djapigo** · 01-11-2013, 07:19 PM

Try a VLOOKUP... check the attachment...

Set up a table of commissions (must be sorted by Units), then use a VLOOKUP (with TRUE as the 4th variable for "approximation")

Look in the help (F1) to learn more about VLOOKUPs...

let me know what you think,
Dennis

**Glh222** · 01-11-2013, 08:02 PM

Thanks...anyone know how I would write the formula? Vlookup is above my skill level so far

**FDibbins** · 01-11-2013, 08:08 PM

without seeing what you are working with, its almost impossibe to suggect a formula. the vlookup syntax is...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**djapigo** · 01-11-2013, 08:18 PM

If you don't like the VLOOKUP formula... try this Nested IFs...

Commission Percentage: =IF(B2>10,15%,IF(B2>=7,14%,IF(B2>=3,13%,0%)))

where B2 is the number of units

**Glh222** · 01-12-2013, 06:16 PM

I tried this formula, but something isnt right...any ideas what im doing wrong??

=LOOKUP(B10,{0,4,8,12,15,99},{0.13,0.14,0.15,0.16,0.17})*G10

b10 is my unit count, g10 is the gross profit column that I want multiplied in order to calculate commision.

**6StringJazzer** · 01-12-2013, 10:19 PM

Your formula looks fine. It says:

Please Login or Register  to view this content.

What is the something not right?

**Glh222** · 01-15-2013, 12:02 PM

For some reason it isnt working properly and for the life of me I cant figure out why.....for instance, when i put a quantity in B10 of 2, its giving me a result of "0", and if I insert a B10 quantity of 18 it returns a value of a 16%, not 17%. Its like the whole scale is off somewhere....any ideas? thanks again

Originally Posted by Glh222

I tried this formula, but something isnt right...any ideas what im doing wrong??

=LOOKUP(B10,{0,4,8,12,15,99},{0.13,0.14,0.15,0.16,0.17})*G10

b10 is my unit count, g10 is the gross profit column that I want multiplied in order to calculate commision.

**djapigo** · 01-15-2013, 02:42 PM

Glh,

18 should return 0.16... count your entries... {0,4,8,12,15,99} has 6 entries and {0.13,0.14,0.15,0.16,0.17} has 5 entries... (6StringJazzer points this out, either they should have the same number of entries or else you get a #N/A results)

However, I'm not sure why 2 is returning 0... are you computing it with G10?

**6StringJazzer** · 01-15-2013, 06:46 PM

What are you using for G10?

Here is an example with all numbers from 0-100 and it gives answer like I described in my post above. I am using 1 in column G to keep the multiplication in place but not confuse the issue.

How is yours different?