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
after that I cant get it to WORK CAN YOU PLEASE HELP ME.
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.

Aladin

=========

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

Aladin,
I keep coming up with this problem what am I doing wrong? Here is what the formula looks like:
=IF(LEN(D5),H5*VLOOKUP(D5,{"Freestyle",0.072;"Airwalk",0.056;"Airwalk majors",0.04;"Freestyle Majors",0.04;"Hickory",0.04;"High Sierra",0.056;"High Sierra Majors",0.04;"Hyde",0.08},2,0),""){"Manzella",0.08;"Manzella Majors",0.04;"Optic Nerve",0.096;"Reliance Majors",0.048;"Sneaker Balls Majors",0.08;"Sorbothane",0.056;"Sorbothane Majors",0.056;"SportHill",0.064,"Terramar",0.042}

Posted by Aladin Akyurek on October 23, 2001 11:58 PM

See: 2732.html. (NT)