Logical ELSE statements

[Guest]

ELSE statements appear to be unavailable. How would I write this CALCULATOR
to produce the correct Paid Out Commission given: Data entered by a person
is 1) Gross Commissions (D5), 2) Product Code (D6), 3) Level 1,2,3,or 4
payment mode (D7). Product codes are in an Excel worksheet 'NY ONLY' column
b/rows 4-225; Levels 1 to 4 in cols h-k. The 'NY ONLY' worksheet contains
the Level 1-4 payment %'s that I'm trying to multiply Gross Commissions (D5)
by. Current logic expression works for one product: =IF(AND($D$6='NY
ONLY'!$B$4, $D$7=1), $D$5*'NY ONLY'!$H$4, 0) NOTE: D5 Commissions=$2,000;
Product Code entered by person does a match on row 4 of 'NY ONLY' sheet;
H4=51.5%, Level=1; COMMISSION TO BE PAID =$1,030.00

QUESTION: How to string multiple IF/AND statements together for the 200+
products and Levels 1 to 4 and have it return the Commission to be Paid in an
answer cell.

 

[Frank Rudd via OfficeKB.com]

Without actually looking at it (sorry, I'm a visual person) I couldn't tell
you for certain, although I have a similar spreadhsheet that calculates
incentive pay based on speed, number of errors, type of error, and hours
worked. I used the vlookup function with several tables to accomplish this,
with the idea that if the method of paying the incentive changes, as it does
periodically, I won't have to change the entire spreadsheet. In your case
your IF statement could then reference the proper table based on the product
code. This doesn't use VBA, but it should work.

 

[Guest]

Please clarify something for me. What kind of information is on the NY ONLY
worksheet for each Product Code/Level? Is it the commission %? If so, I
believe you can get that using the following formula:

=VLOOKUP(D6,'NY ONLY'!$B$2:$L$225,6+Sheet1!D7,FALSE)

You may need to add some $ as necessary. I haven't tested it thoroughly.