I Need a formula to evaluate a cell with + or - values

[Bob in Oklahoma]

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(B1>C1,B1,C1),IF(B1=0,0,IF(D1="Final",B1,IF(B1>C1,B1,C1))))
The problem seems to be with the last step of the formula IF(B1>C1,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(B1>C1,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

 

[Ron Rosenfeld]

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

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(B1>C1,B1,C1),IF(B1=0,0,IF(D1="Final",B1,IF(B1>C1,B1,C1))))
The problem seems to be with the last step of the formula IF(B1>C1,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(B1>C1,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

 

[Bob in Oklahoma]

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


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

OK ... here it is.

The value of A1 is conditioned on the value of B1, C1, & D1. It is never
manually changed.

A1 is a numeric cell. There are 3 possibilities for the value in A1; 0,
positive number, negative number. Never Blank (empty).

B1 is a numeric cell. There are 4 possibilities for the value in B1; Blank
(empty), 0, positive number, negative number. B1 always starts with a Blank
(empty) value.

C1 is a numeric cell. There are 3 possibilities for the value in C1; 0,
positive number, negative number. Never Blank (empty). Usually the value in
C1 does not change after the start value is input. C1 is never updated by
changes to A1, B1.

* D1 is a text cell. There are 2 possibilities for the value in D1;
“Finalâ€, Blank (empty). D1 always starts with a Blank (empty) value. The
value is never changed to “Final†if B1 is Blank (empty). D1 is never
updated by changes to A1, B1, C1.

The logic goes like this:

Since B1and D1 are Blank at the start, then A1 should be the same value as C1.

If I change B1 to 0 then A1 should change to 0; C1 & D1 do not change.

* The following assumes that B1 is not Blank (empty) hence the statement
above. If I change D1 to “Finalâ€, A1 should change to the same value as B1.

If C1 is a positive value and I change B1 to a positive value;
A1 should not change if B1 is smaller than C1, but A1 should change to the
value of B1 if B1 is larger than C1.
Example: A1=100, B1=Blank, C1=100. I change B1 to 50 and A1 does not change
but if I change B1 to 150 then A1 changes to 150.

IF C1 is a negative value and I change B1 to a negative value;
A1 should not change if B1 is a smaller or lesser negative value than C1,
but should change to the same value of B1 if B1 is a greater or larger
negative value than C1. Example: A1=-100, B1=Blank, C1=-100. I change B1 to
-50 and A1 does not change but if I change B1 to -150 then A1 changes to -150.

IF C1 is a positive value and I change B1 to a negative value;
A1 should change to the same negative value as B1.

If C1 is a negative value and I change B1 to a positive value;
A1 should change to the same positive value as B1.

 

[Ron Rosenfeld]

OK ... here it is.

The value of A1 is conditioned on the value of B1, C1, & D1. It is never
manually changed.

A1 is a numeric cell. There are 3 possibilities for the value in A1; 0,
positive number, negative number. Never Blank (empty).

B1 is a numeric cell. There are 4 possibilities for the value in B1; Blank
(empty), 0, positive number, negative number. B1 always starts with a Blank
(empty) value.

C1 is a numeric cell. There are 3 possibilities for the value in C1; 0,
positive number, negative number. Never Blank (empty). Usually the value in
C1 does not change after the start value is input. C1 is never updated by
changes to A1, B1.

* D1 is a text cell. There are 2 possibilities for the value in D1;
“Final”, Blank (empty). D1 always starts with a Blank (empty) value. The
value is never changed to “Final” if B1 is Blank (empty). D1 is never
updated by changes to A1, B1, C1.

The logic goes like this:

Since B1and D1 are Blank at the start, then A1 should be the same value as C1.

If I change B1 to 0 then A1 should change to 0; C1 & D1 do not change.

* The following assumes that B1 is not Blank (empty) hence the statement
above. If I change D1 to “Final”, A1 should change to the same value as B1.

If C1 is a positive value and I change B1 to a positive value;
A1 should not change if B1 is smaller than C1, but A1 should change to the
value of B1 if B1 is larger than C1.
Example: A1=100, B1=Blank, C1=100. I change B1 to 50 and A1 does not change
but if I change B1 to 150 then A1 changes to 150.

IF C1 is a negative value and I change B1 to a negative value;
A1 should not change if B1 is a smaller or lesser negative value than C1,
but should change to the same value of B1 if B1 is a greater or larger
negative value than C1. Example: A1=-100, B1=Blank, C1=-100. I change B1 to
-50 and A1 does not change but if I change B1 to -150 then A1 changes to -150.

IF C1 is a positive value and I change B1 to a negative value;
A1 should change to the same negative value as B1.

If C1 is a negative value and I change B1 to a positive value;
A1 should change to the same positive value as B1.

Try this:

=IF(D1="Final",B1,
IF(ISBLANK(B1),C1,
IF(OR(B1=0,D1="Final",SIGN(B1)=SIGN(C1)),
MAX(ABS(B1),ABS(C1))*SIGN(B1),
IF(SIGN(B1)<>SIGN(C1),B1))))


--ron

 

[Bob in Oklahoma]

You rock Ron, Thanks!
I don't understand the mechanics but it works and I'm greatful. I'll be a
long time trying to learn how it works.

 

[Ron Rosenfeld]

Glad it works for you. Thanks for the feedback.

You should be able to tease out the meaning by looking at it line by line, and
comparing it to what you specified.

SIGN is a function that returns -1 if the number is negative, and +1 if the
number is positive (and 0 if the number is 0).

ABS returns the positive value of any number; so both -15 and +15 would return
+15.

You rock Ron, Thanks!
I don't understand the mechanics but it works and I'm greatful. I'll be a
long time trying to learn how it works.

--ron

 

[Bob in Oklahoma]

That explanation also helps ... thanks again.

Glad it works for you. Thanks for the feedback.

You should be able to tease out the meaning by looking at it line by line, and
comparing it to what you specified.

SIGN is a function that returns -1 if the number is negative, and +1 if the
number is positive (and 0 if the number is 0).

ABS returns the positive value of any number; so both -15 and +15 would return
+15.


--ron