Excel formula based on date variables

I'm trying to create a fee calculator but I can't seem to get the formulas to work for me. I'm essentially trying to write one long formula that takes into account all of the different date variables. I have six selectable products and I've been trying to use a combination of IF, AND and OR statements.

My columns are set up like this:

List of products:

Product 1

Product 2

Product 3

Product 4

Product 5

Product 6

I want the formula in C2 to return:

"No" under "Fee Payable" for the following:

IF Product 5 or Product 6 are in A2, OR
IF Product 3 OR 4 are in A2 AND the date in B2 is less than 18/12/2014, OR
IF there is any product in A2 AND the date in B2 is less that 29/03/2012

"Yes" for the following:

IF Products 3 or 4 are in A2 AND IF B2 is greater than or equal to 18/12/2014, OR
IF Products 1 or 2 are in A2 AND IF B2 is greater than or equal to 29/03/2012

I would like C2 remain blank until something is entered into A2.

Leo Reeves

Posted 2016-04-25T17:38:30.033

Reputation: 33

Answers

Nothing too creative here - just a brute force set of IF statements. Column C contains this formula filled down ...

=IF(OR(A2=$E$6,A2=$E$7,AND(OR(A2=$E$4,A2=$E$5),B2<DATE(2014,12,18)),AND(A2<>"",B2<DATE(2012,3,29))),"No",IF(OR(AND(OR(A2=$E$4,A2=$E$5),B2>=DATE(2014,12,18)),AND(OR(A2=$E$2,A2=$E$3),B2>=DATE(2012,3,29))),"Yes",""))

And generated these results. Note: I used Column E to hold a list of Valid Products. Note 2: Added test cases as per Scott's suggestion ...

OldUgly

Posted 2016-04-25T17:38:30.033

Reputation: 345

Arguably, your answer presentation would be better if you added A20, A21 and A22 = "Product 7" (or something else not on the list) and A23, A24 and A25 = blank. And, strictly speaking, you don't need the $s before the Es. Otherwise, this answer is perfect. P.S. I was using Fortran before 1977. – Scott – 2016-04-25T20:55:22.217

Thanks @Scott - A20 and A23 are non-trivial and should be shown. Updated the screen cap. – OldUgly – 2016-04-26T03:01:07.907

I would like to suggest improvise Formula which is smaller than previous.

The Reasons are,

For NO, Instead of testing product 5 & 6 with date and as OR test Product 3 and 4 separately, better we test them using One Single statement since is technically similar, better & reduces the length of Formula.

I’ve tested Products 3, 4, 5 & 6 with Date value 29/03/2012 coz if the Date is less than it means the Date is naturally smaller than 18/12/2014.

And if A2 is Blank no need to Test further Coz, your last condition is if A2 blank C2 remain blank also,so that I’ve tested it at First.

Likewise I’ve tested For YES, Product 1, 2, 3 & 4 with date value 18/12/2014, since if the date value in cell is either greater than or equals to it means is higher value date than 29/03/2012.

NB: If you compare both results you find difference in Row 9 and 12. Reason is simple Date values in both the rows are greater than the date vales we are testing with.

Please change the Data Range in your Formula as your need.

My Data Range is I79:K99, Formula is in K79.

Sample Column is Product Name & Date to Match has data range M79:M88. Column is Green holds answer produced by previous Formula.

Rajesh S

Posted 2016-04-25T17:38:30.033

Reputation: 6 800