Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Logical ELSE statements
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. |
#2
|
|||
|
|||
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. -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
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. "Ruth" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=IF logical test to search only part of a cell | Excel Worksheet Functions | |||
Nested IF statements | Excel Worksheet Functions | |||
Logical Function - Multiple IF statements | Excel Worksheet Functions | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions | |||
If statements | Excel Worksheet Functions |