On Fri, 28 Oct 2005 12:09:06 -0700, Bob in Oklahoma <Bob in
wrote:

The formula in Cell A1 evaluates the values in B1, C1, and D1. B1 can be
blank or have numerical values either positive or negative. C1 always has
numerical values but can be positive or negative. D1 is either blank or has
a text value of "Final". The value of A1 changes based on the condition of
B1 & D1. There are four possible conditions for B1; Blank, 0, positive value,
or negative value. Here is the formula that I have which works as long as
the values in B1 & C1 are both positive.
=IF(B1="",IF(B1C1,B1,C1),IF(B1=0,0,IF(D1="Final" ,B1,IF(B1C1,B1,C1))))
The problem seems to be with the last step of the formula IF(B1C1,B1,C1 ...
for a negative condition in both B1 and C1 the formula has to be
IF(B1<C1,B1,C1. And then there is the possibility of positive condition in
one cell or the other with a negative value in the opposite cell. Is there
another operator that I can use that elaluates B1 & C1 for a positive or
negative condition and changes the value in A1 as follows;
IF(B1="",C1, --- changes the value in A1 to the value in C1 if B1 is
blank
IF(B1=0,0, --- changes the value in A1 to the value in B1 if B1 is 0
IF(D1="Final",B1 --- changes the value in A1 to the value in B1 if D1 is
"Final"
IF(B1C1,B1 --- changes the value in A1 to the value in B1 if B1 & C1
both have positive values
IF(B1<C1,B1 --- changes the value in A1 to the value in B1 if B1 & C1
both have negative values

Your specifications are not clear. There are conflicts. That may be
contributing to your coding difficulties.

For example

1. If B1 is blank and D1="Final", should A1 display C1 or B1 or something
else?
2. What should be displayed if B1 is positive and C1 is negative?
3. In Excelspeak, BLANK means EMPTY. Is that what you mean, also? Or is
there some formula there?

As you write your description, your formula could be:

=IF(AND(D1="Final",ISBLANK(B1)),"undefined",
IF(ISBLANK(B1),C1,
IF(OR(B1=0,D1="Final",SIGN(B1)=SIGN(C1)),B1,
IF(SIGN(B1)<SIGN(C1),"undefined"))))

But I think you need to further clarify your logic. Perhaps this formula will
help.
--ron