Need some function help...

[TR Young]

I have this function in a particular cell;

=IF(D55>0,IF(AND(E18=1,D14<26),6,IF(AND(E17&E18="10",D14>=26),"0",IF(E17=1,IF(D14<36.344,"0",IF(AND(D14>=36.344,D14<39.549),1,2)),IF(D14<37.11,"0",IF(AND(D14>=37.11,D14<39.174),3,4))))),0)

I need to add the following to it, and I can't figure out how to do it
without having 7 nested 'IF' statements;

=IF(AND(D14<36.344,E17=1),"0",IF(AND(D14<37.11,E17=2),"0","X"))

In addition, is it possible to somehow merge the functions that require a
returned value of "0" with 'OR' statements so that I can minimize my nested
'IF' statements? I can't glean too much information from the 'help'
examples that is provided with Excel, or from the on-line help.

The reason that I have quotations around the zeros is because I need the
returned value of zero do display, and I have 'display zero values' turned
off for a particular reason.

Thank you in advance!

 

[Scott]

It might be easier to provide you with a solution if you enumerate in a
list what conditions result in what values, and what precedence they
have. That way, we can help you with the simplification without
compromising your results.

ie. Put an * where the value can be anything. Also, are E17/E18
limited to 0 or 1 as values?

IF(D55<=0, E17=*, E18 = *, D14 = *) -> 0 (Highest precedence)
IF(D55>0, E17=*, E18=1,D14<26) -> 0 (Next)
.... etc.

Scott

 

[Scott]

It might be easier to provide you with a solution if you enumerate in a
list what conditions result in what values, and what precedence they
have. That way, we can help you with the simplification without
compromising your results.

ie. Put an * where the value can be anything. Also, are E17/E18
limited to 0 or 1 as values?

IF(D55<=0, E17=*, E18 = *, D14 = *) -> 0 (Highest precedence)
IF(D55>0, E17=*, E18=1,D14<26) -> 0 (Next)
.... etc.

Scott

 

[TR Young]

D55 is either going to be 0 or the name of an extrusion we use. If D55=0,
then the rest of the function is null.
E17 and E18 are limited to 0 and 1.
D14 is a number which will be entered by a user. The number can be any
number between 20 and 39.549.

Hope that helps!

 

[Scott]

A bit, but not completely. Looking at your formula, some of the
statements seem to conflict with each other.

Based on your formula, it seems that you want this:
E17 = 1, E18 = 1, then for D14:
a) 6 for [20, 26)
b) 0 for [26, 36.344)
c) 1 for [36.344, 39.549)
d) 2 for [39.549]

Note that square brackets mean that the value on that side is included
in the range, and round brackets mean that the value on that side is
excluded. For example, in (a), 26 is followed by a round bracket, so
it is not in this range. In (b), it is after a square bracket, so it
is included in this range, and would return a value of 0. (Just
clarifying in case you're not familiar with the notation.)

If you could break down the range like I have, so that all values for
D14 from 20 to 39.549 are covered for each combination:

Assuming D55 > 0, of course.

(1) E17 = 1, E18 = 1

(2) E17 = 1, E18 = 0

(3) E17 = 0, E18 = 1

(4) E17 = 0, E18 = 0

Hopefully that makes sense.

Scott

 

[TR Young]

Thanks for you help; I finally figured out how to do it.