Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula HELP
Okay, I can see where you are coming from. If I have a value see examples
below: C44 = 500 and C45 = 225. What approach would you suggest to use by placing an "X" in cell C = "X" to make cell C44 = 725 and make cell C45 = 0? Effectively rolling the value from cell C45 into cell C44. I await your reply. Thanks again for your valued input. "Frank Kabel" wrote: Hi so you already have a manual entered value in cell C45?. if yes: you can't have both in one cell: a formula and a cell value. Formulas can't change the content of other cells -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... Thanks, Frank. What about the first formula? The second one as stated works fine, it's the first that is not working well. It is as follows: =IF($C$1="X",0,(C45)). I want to make this cell C45 = 0 if C1 = "X". If C1 = "" make C45 = to real value, whatever it is! Thanks,...again for your input. "Frank Kabel" wrote: Hi not really sure what you're trying to achieve. could you give some examples. Note: The second formula is much to complicated. try: =C44+IF(C1="X",C45,0) -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... I am using these two formulas to clear one cell of it's values and put in a 0 instead of it's original value: =IF($C$1="X",0,(C45)), then roll-up that value into the value of the cell above in cell C44: =SUM((SUM(C44)+IF(C1="X",(C45),0))). However, I'd like to leave in place any original value (if there is a value) in cell C45 if the C1 = "". Only make C45 = 0 if C1 = "X". Hope I explained thing correctly. The second (function) part work well, it's the first (function) part that fails. Thanks, |
#2
|
|||
|
|||
Hi
as said not possible with formulas. You'll need a macro (VBA) for this. The question remains why not use a helper cell :-) -- Regards Frank Kabel Frankfurt, Germany Jay wrote: Okay, I can see where you are coming from. If I have a value see examples below: C44 = 500 and C45 = 225. What approach would you suggest to use by placing an "X" in cell C = "X" to make cell C44 = 725 and make cell C45 = 0? Effectively rolling the value from cell C45 into cell C44. I await your reply. Thanks again for your valued input. "Frank Kabel" wrote: Hi so you already have a manual entered value in cell C45?. if yes: you can't have both in one cell: a formula and a cell value. Formulas can't change the content of other cells -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... Thanks, Frank. What about the first formula? The second one as stated works fine, it's the first that is not working well. It is as follows: =IF($C$1="X",0,(C45)). I want to make this cell C45 = 0 if C1 = "X". If C1 = "" make C45 = to real value, whatever it is! Thanks,...again for your input. "Frank Kabel" wrote: Hi not really sure what you're trying to achieve. could you give some examples. Note: The second formula is much to complicated. try: =C44+IF(C1="X",C45,0) -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... I am using these two formulas to clear one cell of it's values and put in a 0 instead of it's original value: =IF($C$1="X",0,(C45)), then roll-up that value into the value of the cell above in cell C44: =SUM((SUM(C44)+IF(C1="X",(C45),0))). However, I'd like to leave in place any original value (if there is a value) in cell C45 if the C1 = "". Only make C45 = 0 if C1 = "X". Hope I explained thing correctly. The second (function) part work well, it's the first (function) part that fails. Thanks, |
#3
|
|||
|
|||
You want a formula that will change values in 2 cells: take the value from
C45, add it to C44 and subtract it from itself.. As Frank said, an "ordinary" formula cannot change two cells. An array formula can return results to 2 cells, but in this case the inputs to the formula would be the cells containing the formula, which would create a circular reference. You need a VBA macro to do this. On Wed, 27 Oct 2004 09:29:07 -0700, Jay wrote: Okay, I can see where you are coming from. If I have a value see examples below: C44 = 500 and C45 = 225. What approach would you suggest to use by placing an "X" in cell C = "X" to make cell C44 = 725 and make cell C45 = 0? Effectively rolling the value from cell C45 into cell C44. I await your reply. Thanks again for your valued input. "Frank Kabel" wrote: Hi so you already have a manual entered value in cell C45?. if yes: you can't have both in one cell: a formula and a cell value. Formulas can't change the content of other cells -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... Thanks, Frank. What about the first formula? The second one as stated works fine, it's the first that is not working well. It is as follows: =IF($C$1="X",0,(C45)). I want to make this cell C45 = 0 if C1 = "X". If C1 = "" make C45 = to real value, whatever it is! Thanks,...again for your input. "Frank Kabel" wrote: Hi not really sure what you're trying to achieve. could you give some examples. Note: The second formula is much to complicated. try: =C44+IF(C1="X",C45,0) -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... I am using these two formulas to clear one cell of it's values and put in a 0 instead of it's original value: =IF($C$1="X",0,(C45)), then roll-up that value into the value of the cell above in cell C44: =SUM((SUM(C44)+IF(C1="X",(C45),0))). However, I'd like to leave in place any original value (if there is a value) in cell C45 if the C1 = "". Only make C45 = 0 if C1 = "X". Hope I explained thing correctly. The second (function) part work well, it's the first (function) part that fails. Thanks, |
#4
|
|||
|
|||
Will this work for you?
=IF(AND(A15,A1<10),1,"") "LRabourn" wrote: I'm trying to create a formula that says "if the value in A1 is greater than 5 and less than 10" return a "1" otherwise leave blank. note: column A are all numeric values |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) |