| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting cells and getting a null return
I need to to subtract two cells. If the cell is empty I need a null return.
e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
This formula tests for both A and B being empty, and returns the
subtraction only if both have values in them: C1: =IF(AND(A1="",B1=""),"",A1-B1) I'm not sure from your examples if you are subtracting A from B or the other way round. Hope this helps. Pete Nick Horn wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
Sorry, scrub that. Make it:
C1: =IF(AND(A1<"",B1<""),A1-B1,"") The formula can be copied down as far as you need. Pete Pete_UK wrote: This formula tests for both A and B being empty, and returns the subtraction only if both have values in them: C1: =IF(AND(A1="",B1=""),"",A1-B1) I'm not sure from your examples if you are subtracting A from B or the other way round. Hope this helps. Pete Nick Horn wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
=IF(OR(A1="",B1=""),"",A1-B1)
"Nick Horn" wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
Hi many thanks for your help on both my queries.
The cells that you gave me the formula for (below) uses conditional formatting, highliting the cell green if greater than 4 and blue if less than -4. Using your formula results in the cell highlighting Green - any ideas as to why? I can live with the problem so if it takes up too much of your time please don't wory about it. Best wishes Nick "Teethless mama" wrote: =IF(OR(A1="",B1=""),"",A1-B1) "Nick Horn" wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
What's your actual CF formula?
Do you have an un-addressed gap between 4 and -4? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nick Horn" wrote in message ... Hi many thanks for your help on both my queries. The cells that you gave me the formula for (below) uses conditional formatting, highliting the cell green if greater than 4 and blue if less than -4. Using your formula results in the cell highlighting Green - any ideas as to why? I can live with the problem so if it takes up too much of your time please don't wory about it. Best wishes Nick "Teethless mama" wrote: =IF(OR(A1="",B1=""),"",A1-B1) "Nick Horn" wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
Not entirley sure if I have understood you - but the Conditional Formatting
is done through the Format option on the Toolbar and is: Condition 1 Cell value is less than -4 then I have pattern as blue and font white Condition 2 Cell value is greater than 4 then I have pattern as green and font automatic(black) Can't see any gaps with the 4 -4. "Ragdyer" wrote: What's your actual CF formula? Do you have an un-addressed gap between 4 and -4? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nick Horn" wrote in message ... Hi many thanks for your help on both my queries. The cells that you gave me the formula for (below) uses conditional formatting, highliting the cell green if greater than 4 and blue if less than -4. Using your formula results in the cell highlighting Green - any ideas as to why? I can live with the problem so if it takes up too much of your time please don't wory about it. Best wishes Nick "Teethless mama" wrote: =IF(OR(A1="",B1=""),"",A1-B1) "Nick Horn" wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting cells and getting a null return
The gap is between -4 an 4.
Your conditions stipulate *LESS* then -4, AND *GREATER* then 4, SO ... Tell me ... where does "2" get addressed? It's *not* less then -4, AND It's *not* greater then 4 !?!? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nick Horn" wrote in message ... Not entirley sure if I have understood you - but the Conditional Formatting is done through the Format option on the Toolbar and is: Condition 1 Cell value is less than -4 then I have pattern as blue and font white Condition 2 Cell value is greater than 4 then I have pattern as green and font automatic(black) Can't see any gaps with the 4 -4. "Ragdyer" wrote: What's your actual CF formula? Do you have an un-addressed gap between 4 and -4? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Nick Horn" wrote in message ... Hi many thanks for your help on both my queries. The cells that you gave me the formula for (below) uses conditional formatting, highliting the cell green if greater than 4 and blue if less than -4. Using your formula results in the cell highlighting Green - any ideas as to why? I can live with the problem so if it takes up too much of your time please don't wory about it. Best wishes Nick "Teethless mama" wrote: =IF(OR(A1="",B1=""),"",A1-B1) "Nick Horn" wrote: I need to to subtract two cells. If the cell is empty I need a null return. e.g. A1 B1 C1 22 20 2 10 8 -2 10 The value in C1 needs to be blank, so that when the value in A1 is added later then C1 will recalculate. The resultant calculated values will be used in mean, standard deviation and COUNTIF calculations. As always many thanks to all of you out there - you are doing a brilliant job for duffers like me. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
