| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
I Need a formula to evaluate a cell with + or - values
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 |
|
#2
|
|||
|
|||
|
I Need a formula to evaluate a cell with + or - values
|
|
#3
|
|||
|
|||
|
I Need a formula to evaluate a cell with + or - values
"Ron Rosenfeld" wrote: 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 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. |
|
#4
|
|||
|
|||
|
I Need a formula to evaluate a cell with + or - values
On Fri, 28 Oct 2005 20:22:01 -0700, Bob in Oklahoma
wrote: "Ron Rosenfeld" wrote: 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 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 |
|
#5
|
|||
|
|||
|
I Need a formula to evaluate a cell with + or - values
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" wrote: On Fri, 28 Oct 2005 20:22:01 -0700, Bob in Oklahoma wrote: "Ron Rosenfeld" wrote: 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 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 |
|
#7
|
|||
|
|||
|
I Need a formula to evaluate a cell with + or - values
That explanation also helps ... thanks again.
"Ron Rosenfeld" wrote: 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. =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)))) On Sun, 30 Oct 2005 17:49:02 -0800, Bob in Oklahoma wrote: 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" wrote: On Fri, 28 Oct 2005 20:22:01 -0700, Bob in Oklahoma wrote: "Ron Rosenfeld" wrote: 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 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 --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text in formula bar is not displaying in cell | Excel Discussion (Misc queries) | |||
| Refreshing drop down cell values... | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
Linear Mode
