improve formula, Possible Genious needed !!!

[Corey]

=IF(AND(OR(C5=V13,C5=V15,C5=V16),C8>C7,(C8-C7)*24>W10,(V2-C7)*24>=W13),W13,IF(AND(C5=V14,(V2-C7)*24>=W14),W14,IF(AND(OR(C5=V13,C5=V15,C5=V16),C7<V2,(V2-C7)*24<W13),(V2-C7)*24,IF(AND(C5=V14,C7<V2,(V2-C7)*24<W14),(V2-C7)*24,IF(AND(OR(C5=V13,C5=V15,C5=V16),C7>C8,OR(C8<V2,C8>W2)),W13,IF(AND(C5=V14,C7>C8,OR(C8<V2,C8>W2)),W14,IF(AND(C7>=W2,C5<>V9,C5<>V10,C5<>V11,C5<>V12,C5<>V18,C5<>V19,C5<>V14),W13,0)))))))

i HAVE APARENTLY RAN OUT OF OPTIONS (7) IN THIS FORMULA.
bUT NEED MORE OPTIONS YET.
iS THER ANYONE WHO HAS THE INTELIGENCE TO LESSEN THE ABOVE FORMULA TO ALLOW FOR YET MORE OPTIONS?

CTM

 

[Bob Phillips]

I would break it down, putting conditions in separate cells and test those.
It's too unwieldy to maintain as is.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

=IF(AND(OR(C5=V13,C5=V15,C5=V16),C8>C7,(C8-C7)*24>W10,(V2-C7)*24>=W13),W13,I
F(AND(C5=V14,(V2-C7)*24>=W14),W14,IF(AND(OR(C5=V13,C5=V15,C5=V16),C7<V2,(V2-
C7)*24<W13),(V2-C7)*24,IF(AND(C5=V14,C7<V2,(V2-C7)*24<W14),(V2-C7)*24,IF(AND
(OR(C5=V13,C5=V15,C5=V16),C7>C8,OR(C8<V2,C8>W2)),W13,IF(AND(C5=V14,C7>C8,OR(
C8<V2,C8>W2)),W14,IF(AND(C7>=W2,C5<>V9,C5<>V10,C5<>V11,C5<>V12,C5<>V18,C5<>V
19,C5<>V14),W13,0)))))))

i HAVE APARENTLY RAN OUT OF OPTIONS (7) IN THIS FORMULA.
bUT NEED MORE OPTIONS YET.
iS THER ANYONE WHO HAS THE INTELIGENCE TO LESSEN THE ABOVE FORMULA TO ALLOW
FOR YET MORE OPTIONS?

CTM

 

[Guest]

Corey,

Bob is dead right, but if you need more options, then for your last nested
if use something like

cell B1
....,IF(condition,value_if_true,"SOMETHING DIFFERENT")))))))

then you can have another column which starts off like this

cell C1
=IF(B1<>"SOMETHING DIFFERENT","", IF(... ))

and another column which puts them back together

cell D1
=IF(C1="",B1,C1)

 

[vezerid]

Hi,

I tooke your formula and changed lines each time we had a new IF. It
looks as follows:

=IF(AND(OR(C5=V13,C5=V15,C5=V16),C8>C7,(C8-C7)*24>W10,(V2-C7)*24>=W13),W13,
IF(AND(C5=V14,(V2-C7)*24>=W14),W14,
IF(AND(OR(C5=V13,C5=V15,C5=V16),C7<V2,(V2-C7)*24<W13),(V2-C7)*24,
IF(AND(C5=V14,C7<V2,(V2-C7)*24<W14),(V2-C7)*24,
IF(AND(OR(C5=V13,C5=V15,C5=V16),C7>C8,OR(C8<V2,C8>W2)),W13,
IF(AND(C5=V14,C7>C8,OR(C8<V2,C8>W2)),W14,
IF(AND(C7>=W2,C5<>V9,C5<>V10,C5<>V11,C5<>V12,C5<>V18,C5<>V19,C5<>V14),W13,
0)))))))

From what we see, your various options return one of the following

three expressions:
W13,
W14,
(V2-C7)*24

When we want to return N values with IF we can do it with N-1 IF's.
IF(cond1,Result1,IF(cond2,Result2,IF(...)))

You can group all the conditions returning the same result with an OR.
Then you will need only two IF's for 3 possible outcomes.

Also consider the following two approaches:
IF(cond1,Result1,IF(cond2,Result2,IF(...)))
IF(cond1,IF(cond2, ...), IF(cond3,...))

The first case is a degenerate binary tree, which effectively is a
linear structure with depth N-1, hence it requires N-1 levels of
nesting. The second case is non-degenerate, hence it requires O(log(N))
levels of nesting. For the second you need a divide-and-conquer method,
where one common condition is tested and subconditions are left for the
remaining ones.

Another thing you can do is, since several conditions appear frequently
(e.g. OR(C5=V13,C5=V15,C5=V16)), you can define named formulas,
alhtough this will only simplify the appearance and will not, per se,
reduce levels of nesting.

But then again maybe Harlan will come up with one of his impressive
solutions to such problems.

HTH
Kostis Vezerides

 

[Guest]

Hi.
I step up here cause I do have the same problem. Can you please inform us if
the contiguous if's can be joined by OR for the the necessary same result
(i.e. in its order as W13 then W14 then (V2-C7)*24) )....
I think you have a sequence of IF's that are constrained by a definite
sequence considering that in the....
1st IF....this contain a definite constraint of C8>C7 : RESULT is W13
5th IF....this contain a definite constraint of C7>C8 : RESULT is W13
maybe we can add another IF upon your clarification of possible merging by
OR based on agreed constraints vs. RESULT...

 

[Bob Phillips]

If the result is the same, you can definitely do an OR

=IF(OR(C8>C7,C8<C7),W13, ...

but in this instance this also works

=IF(C8<>C 7,W13,...

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[vezerid]

Hi, (if I understand your question)

In a sequence like IF(..., ..., IF(... etc

we can sometimes not change the order without producing erroneous
results. For example if we have two conditions involved and various
outcomes then we must first test the most exclusive condition, i.e. the
one that is hardest to achieve. A classic example is the leap year. If
we do it like:

=IF(MOD(A2,4)=0, ...

then all multiples of 4 will be leap years, which we do not want.
Instead we must start with

=IF(MOD(A2,400)=0,...)

In this case, the fact that we have opposite conditions (C8>C7 and
C7>C8) does not preclude grouping b/c these conditions are conjuncted
with others. In general, sequential IF's have to retain their order if
the same condition participates in two outcomes but there might be an
exception. Again in the leap year example, for a year to be divisible
by 400 it must be divisible also by 100 and 4 while the reverse is not
true. Yet, it is these conditions that are involved in the decision
tree. This is why we use:

=IF(MOD(A2,400)=0,"leap",IF(MOD(A2,100)=0,"non
leap",IF(MOD(A2,4)=0,"leap")))

instead of

=IF(OR(MOD(A2,4)=0,MOD(A2,400)=0),"leap"...

Does this help?
Kostis Vezerides

 

[Guest]

Hi. Bob n VEZERID....

I am directly relating the formulated "AS IS - WHERE IS" problem....to
shorten a "MAYBE" possible true and direct solution....

As i observed, before the 5th IF can be READ with a C7>C8, the 3rd and the
4th IF requires that C7<V2, hence if C7<V2, the 5th IF cannot proceed and
finally the C7<>C8 constraint for the proposed 1st IF(or...may provide other
logic to the formula.....
please correct me if I'm wrong...

 

[Corey]

Worked out the same solution after posting.
I had a few results that had the same cell value, there fore added an OR and
combined the formulas into fewer IF's and therefore leaving me with more
If's possibilities.

Cheers all who replied.