Help with Nested Functions

  • Thread starter Thread starter Comcast News Reader
  • Start date Start date
C

Comcast News Reader

I've been trying for the last several hours to get this right. Here's the
logic I'm trying for:

1. If Column D is earlier than of equal to 7/1/2003, and Column E is
blank, then enter Column B's value in Column M
2. And, if Column D is later than 4/2/2003 and earlier than 7/1/2003, and
Column E is later than 9/30/2003, then enter Column B's value in Column M
3. Or, if Column D is earlier than or equal to 4/1/2003 and Column E is
not blank, and Column E is later than 7/1/2003, then enter Column B's value
in column M
4. Otherwise, enter 0.00 in Column M


Here's the function I've been playing with, and can't get right:

=IF(AND(E137="",D137<=DATE(2003,7,1)),B137,IF(AND(D137>DATE(2003,4,2),D137<D
ATE(2003,7,1),E137>DATE(2003,9,30)),B137,IF(OR(D137<DATE(2003,4,1),NOT(E137=
""),E137>DATE(2003,7,1)),B137,0)))


B D E
M

12,813.21 5/19/2003
9/30/2003 - - - 12,813.21


52,807.50 9/7/2000
10/31/2003 - - - 52,807.50



11,562.01 9/7/2001
4/18/2003 - - - 11,562.01



7,832.00 10/6/2003
2,848.00 7,832.00 0.00



37,075.00 1/6/2003
1,443.00 2,886.00 37,075.00



These are samples of several rows in the spreadsheet.

The first row is incorrect. According to my logic, Column M should be 0.00
instead of 12, 813.21
Column M in the second row is correct, according to my logic.
The third row is incorrect. According to my logic, Column M should be 0.00
instead of 11, 562.01
Column M in the forth row is correct, according to my logic
The fifth row is incorrect. According to my logic, Column M should be 0.00
instead of 37, 075.00


Any help would be greatly appreciated.
 
If there is no text in any of the cells, try this in M1 (not fully tested):

=B1*OR(AND(D1>=DATEVALUE("7/1/2003"),E1=0),AND(D1>=DATEVALUE("4/1/2003"),E1>
=DATEVALUE("10/1/2003")),E1>=DATEVALUE("7/1/2003"))

I allowed for what I thought were a couple of typos. If they weren't, just
adjust the formula accordingly.
 
Try this:

=IF(OR(AND(D137<=DATE(2003,7,1), E137=""), AND(D137>DATE(2003,4,2),
D137<DATE(2003,7,1),E137>DATE(2003,9,30)), AND(D137<=DATE(2003,4,1),
E137>DATE(2003,7,1))), B137, 0)

Note that your third line seems to be wrong - the value in M should
be 0.
 
This repsonse did the trick. The only changes I had to make was to the cell
references.

Thank you very much!
 
Back
Top