Help with IF formula - too many ANDs and ORs for my liking!

[STUARTXL]

Hi All,

I've officially given up. Can anyone see the problem with the formulas contained in Cell D30 on the attached. What I'm trying to achieve is the below - I've tried different permutations myself and the closest I've got is the formula in Cell D30, so I'll use this as an example:

- First rule: If the date in Column A on row 30 is not greater than the date in cell A2, the cell D30 should show zero
- Second rule: If the date in D20 is greater than or equal to the date in A30 AND is also less than the date in A31, then;
- If the date in A30 is greater or equal to the date in D12 AND less than or equal to the date in D14 (i.e. if A30 falls within the date range of D12 and D14), then the result should be D17 plus D19 but;
- If the date in A30 is less than the date in D12 OR greater than the date in D14 (i.e. if A30 falls outside the date range of D12 and D14), then the result should be D17 but;
- Third rule: if the date in D20 is less than A30 or greater than A31 (i.e. falls outside the date range of A30 and A31) then:
- If A30 is greater than or equal to D12 AND less than or equal to D14, then the result should be D19 else;
- The result should be zero

[Philb1]

Hi Stuart

Here is my effort, it works but I haven't tested it properly
It works in the order you described it

Please Login or Register  to view this content.

[thisoldman]

Parsing your text statements, I came up with this.

Pseudocode

Please Login or Register  to view this content.

When using Booleans, multiplication is equivalent to AND(), addition is equivalent to OR(). When trying to explain your statements to myself, I saw no OR() conditions. This is what I came up with:

Please Login or Register  to view this content.