# Nesting Problem ( I think? )

Posted by Marty on October 23, 2001 3:51 PM

I need help with a formula that I will be using to track sales data. I have put together a spread sheet that I will track sales data by companies that I represent which is 17 total companies. I want to track ship dates, cancel dates, date order was written, account name, and most importantly commission. I have found that it works with 7 companies that I have in the formula but after that I keep getting the formulas error message. Here is a copy of the formula:
=IF(D6="Freestyle",+H6*0.072,IF(D6="airwalk",+H6*0.056,IF(D6="airwalk majors",+H6*0.04,IF(D6="Freestyle Majors",+H6*0.04,IF(D6="Hickory",+H6*0.04,IF(D6="High Sierra",+H6*0.056,IF(D6="High Sierra Majors",+H6*0.04,IF(D6="Hyde",+H6*0.08
Thanks,
Marty

Posted by Aladin Akyurek on October 23, 2001 4:06 PM

Marty,

Try:

=IF(LEN(D6),H6*VLOOKUP(D6,{"Freestyle",0.072;"Airwalk",0.056;"Airwalk majors",0.04;"Freestyle Majors",0.04;"Hyckory",0.04;"High Sierra",0.056;"High Sierra Majors",0.04;"Hyde",0.08},2,0),"")

You can put

{"Freestyle",0.072;"Airwalk",0.056;"Airwalk majors",0.04;"Freestyle Majors",0.04;"Hyckory",0.04;"High Sierra",0.056;"High Sierra Majors",0.04;"Hyde",0.08}

-which is a 2-column table- in some cell, select that cell, go to the Name Box on the Formula Bar, and type a name, e.g., COMMISSIONS. You can then use this name in the above formula instead. You can expand this table at will.

=========

Posted by Marty on October 23, 2001 5:10 PM