Argumentive functions, I am sure this an easy fix but I am horrible at Excell

[Grefcon901]

I have a set of "Y"s and "N"s. These are outputted from other data sets that are not pertaining to this problem.

Example:

Y Y N
N N Y
Y N Y

Each set of three outputs has a status, Either "ZERO", "TRUE", or "PLUS"

Any Combination of a "N" in the 1st or 2nd position will always output a "ZERO"

Y Y N = TRUE (always)
Y Y Y = TRUE (if it proceeds a "PLUS")

Y Y Y = "PLUS"

Here is the issue... "ZERO" and "TRUE" are derived from the adjacent 3 conditional cells in that row. The "PLUS" Status is derived from the 3rd adjacent condition, but from the previous row is where it acquires its 1st and 2nd condition. There can not be two "PLUS" statuses one right after another and so it can either be a zero or a plus, but the formula I have so far can not distinguish the post "PLUS" status.



=IF(AND(P4="Y",Q4="Y",R5="Y"),IF(OR(S4="PLUS",(AND(P4="Y",Q4="Y",R4="Y",P5="N",Q5="N",R5="Y"))),"TRUE","PLUS"),IF(AND(P5="Y",Q5="Y"),"TRUE","ZERO"))

[igoodable]

A sample workbook would help.

[Grefcon901]

Here Is a sample

[shg]

Starting in D4, the output depends on 6 Y/N inputs (3 in the row above, plus 3 in the current row), and the value immediately above (T/P/Z).

That's 2^6 * 3 = 192 possibilities, each of which generates a result of T, P, or Z.

Rather than attempt to create a formula with some convoluted logic, you could make a table with 192 rows with all combinations of the inputs as catenated strings, add a column containing the output for each combination, and then just do a lookup.

E.g. ,

YYYYYYTRUE gives whatever
YYYYYYPLUS gives whatever
YYYYYYZERO gives whatever
YYYYYNTRUE gives whatever
...

Then I'd sort those in alphabetical order so I could use a range lookup.

I'm not volunteering, but that's how I'd do it.

[dflak]

You are going to have to spell out the rules in the order of precedence.

YYN is TRUE
IF YYY and Cell Below ="PLUS", TRUE else "PLUS" <- this excludes two plusses in a row.
or is it the first two columns of the row above and the third column of the current row. The text says both.

How do you determine how to assign a ZERO?

[Grefcon901]

Only the PLUS output will look to the previous row. TRUE and ZERO are derived from the adjacent cond. 1 & 2 in that row.

[igoodable]

I don't really understand your problem, but i made an attempt anyways. Here's my understanding of the problem:

1. Any Combination of a "N" in the 1st or 2nd position will always output a "ZERO"
2. YYN will always be "TRUE"
3. Everything else is "PLUS"
4. If there a "PLUS" on the next row, then it should be change to "TRUE"

Please Login or Register  to view this content.

[Grefcon901]

Thank you so much! this did not give me what it needed but the model got me thinking in the correct direction to solve it!

Thanks, Jon