Extended Formula please help!!
Posted by Marty on October 24, 2001 6:53 AM
I have 18 different commission structures I want to be able to track. Each commission structure is different as far as %'s are concerned. I want to be able to track my commissions by the different commission %'s and I am having the hardest time to get the formula to work. The following are my column headers:
C/O
PO#
Account Name
Company
Order Date
Ship Date
Cancel Date
Dollars Sold
Commission Month
Commission
AS I SEE IT I WANT TO BE ABLE TO PUT UNDER THE "COMPANY" COLUMN THE NAME OF THE COMPANY WHERE AN ORDER WAS WRITTEN AND HAVE THE "COMMISSION" COMPUTED. I HAVE GOT IT TO WORK WITH 8 COMPANIES BUT AFTER THAT I THINK I HAVE A NESTING ISSUE THAT I CANT RESOLVE. THE COMMISSION STRUCTURES ARE AS FOLLOWS:
AIRWALK 5.6%
AIRWALK MAJORS 4%
FREESTYLE 7.2%
FREESTYLE MAJORS 4%
HICKORY4%
HIGH SIERRA 5.6%
HIGH SIERRA MAJORS4%
HYDE 8%
MANZELLA 8%
MANZELLA MAJORS 4%
OPTIC NERVE 9.6%
RELIANCE MAJORS 4.8%
SNEAKER BALLS MAJORS 8%
SORBOTHANE 7.2%
SORBOTHANE MAJORS 5.6%
SPORTHILL 6.4%
TERRAMAR 4.2%
THORLO 10%
THANKS IN ADVANCE FOR YOU HELP.
MARTY
Posted by Juan Pablo on October 24, 2001 6:58 AM
Posted by Bob Umlas on October 24, 2001 7:03 AM
Assuming you enter the company name in F2, for example, you can use this formula, where "cos" defines that list of companies you listed:
=SUBSTITUTE(INDEX(cos,MATCH(F2&"*",cos,0)),UPPER(F2),"")*1
HTH
Bob Umlas
Excel MVP
--------------------------------------------
Posted by Aladin Akyurek on October 24, 2001 7:10 AM
Marty,
It seems you had trouble extending the lookup table in array form. Here is another proposal, which is equivalent.
Put the list of companies and percentages in a separate worksheet (the complete list, no matter how big). Select all of the cells of this list, go to the Name Box on the Formula Bar, and type in there COMMISSIONS.
Now go to the cell where you want to compute the commission. As before (see earlier posts), use
=IF(LEN(D5),H5*VLOOKUP(D5,COMMISSIONS,2,0),"")
Note. Still in doubt, send me a copy of your workbook
to have a look at it.
Aladin
==========
Posted by Aladin Akyurek on October 24, 2001 7:52 AM
Sequel
As Bob Umlas suspected, you had problems with company names besides having trouble in implementing "the table".
I named the table of commissions COMMISSIONS and the first column of this tabble housing company names (or types) COMPANIES.
I used the following formula:
=IF(LEN(D5),IF(COUNTIF(COMPANIES,D5)=1,H5*VLOOKUP(D5,COMMISSIONS,2,0),"Company Unknown"),"")
This works of course. It shows that "Sondico" is not in you table (yet).
I'd suggest also looking at Umlas's formula.
Both formulas avoid using a string of (nested) IFs. That's what counts here.
Cheers.
Aladin
PS. The workbook is underway.
=========== Marty, It seems you had trouble extending the lookup table in array form. Here is another proposal, which is equivalent. Put the list of companies and percentages in a separate worksheet (the complete list, no matter how big). Select all of the cells of this list, go to the Name Box on the Formula Bar, and type in there COMMISSIONS. Now go to the cell where you want to compute the commission. As before (see earlier posts), use =IF(LEN(D5),H5*VLOOKUP(D5,COMMISSIONS,2,0),"") Note. Still in doubt, send me a copy of your workbook
