Excel formula with date and integer range test

basscomp

New Member
Joined
Dec 10, 2002
Messages
10
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Joined
Jul 30, 2006
Messages
3,656
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

Active Member
Joined
Oct 18, 2004
Messages
430
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

New Member
Joined
Dec 10, 2002
Messages
10
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
 

Forum statistics

Threads
1,137,199
Messages
5,680,118
Members
419,883
Latest member
overhear

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top