Excel formula with date and integer range test

[basscomp]

Hello,

I am trying to write a nested IF formula to produce values in the farthest right column of my spreadsheet ([Final Tally CommissionAmount]) based on criteria I will list below.

Here are my columns:

PlanOpenDate,
TranAmount,
PMC_Key,
CommissionAmount,
CategoryParameter_ID,
[Final Tally CommissionAmount]

All columns have values except for [Final Tally CommissionAmount]. This last column will hold my formula.

CRITERIA

For each row in my table, I want to store in the [Final Tally CommissionAmount] column a value based on the following:

If CategoryParameter_ID = 2 then [Final Tally CommissionAmount] = TranAmount * 0.005

If CategoryParameter_ID = 1 then check PlanOpenDate. If PlanOpenDate is greater than 2/28/2006 then [Final Tally CommissionAmount] = TranAmount * 0.01. If PlanOpenDate is less than or equal to 2/28/2006 then [Final Tally CommissionAmount] = TranAmount * 0.015

Also if PMC_key is 1, 70 or between 700-799 inclusive, [Final Tally CommissionAmount] = 0

If none of these criteria exist then [Final Tally CommissionAmount] = TranAmount

Any help would be appreciated.

Thanks,
basscomp

 

[stanleydgromjr]

Whate columns are the following fields in?
PlanOpenDate
TranAmount
PMC_Key
CommissionAmount
CategoryParameter_ID

Can we see some test data?


Have a great day,
Stan

 

[Ron Morris]

Hi,

This works but you need to put the if statements in the order of their precedence. I enter the formula based on how you typed out the criteria.

I am assuming that each column of your data is starting in A (formula to be entered in Column F) and the data begins in rows 2.

=IF(E2=2,B2*0.005,IF(AND(E2=1,A2>2/28/2006),B2*0.01,IF(AND(E2=1,A2<=2/28/2006),B2*0.015,IF(OR(C2=1,C2=700),0,IF(AND(C2>=700,C2<=799),0,B2)))))

Hope this helps

Ron

 

[basscomp]

nested ifs and range comparison

Thanks Ron,

This clears up some the comparison problems I was having. I have not tested it out but will let you know how it went. Your assumptions about the column positions were right.

basscomp