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.

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.

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.

=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.

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.

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.

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.

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.

Thanks for that very clear explanation - much appreciated. I have tried
adding a third condition to the conditional formating:

If cell value is between 4 and -4 then no pattern and font automatic

(I also tried between 3 and -3 and reversed them)
but the cell remains green. I guess the issue is around the "".

Many thanks for the help though.



"Ragdyer" wrote:

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.

There is *no* reason to add a 3rd condition!

If done correctly, your CF *should* work as you wish:
Green for 4
Blue for <-4
And "regular" as the default - where the other 2 equate to FALSE.

Meaning, the format comes into force *only* when the condition is TRUE.

Believe me, you have just done something wrong in the CF!
Your concept is OK.

Grab a stiff drink, and start out from the beginning on a new sheet!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Nick Horn" wrote in message
...
Thanks for that very clear explanation - much appreciated. I have tried
adding a third condition to the conditional formating:

If cell value is between 4 and -4 then no pattern and font automatic

(I also tried between 3 and -3 and reversed them)
but the cell remains green. I guess the issue is around the "".

Many thanks for the help though.



"Ragdyer" wrote:

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.

Having a second look, I think you might be better off using
"Formula Is",
And including that null in the stipulation.

Something like:

=AND(A14,A1<"")
For green,

And
=AND(A1<-4,A1<"")
For blue.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
There is *no* reason to add a 3rd condition!

If done correctly, your CF *should* work as you wish:
Green for 4
Blue for <-4
And "regular" as the default - where the other 2 equate to FALSE.

Meaning, the format comes into force *only* when the condition is TRUE.

Believe me, you have just done something wrong in the CF!
Your concept is OK.

Grab a stiff drink, and start out from the beginning on a new sheet!

--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Nick Horn" wrote in message
...
Thanks for that very clear explanation - much appreciated. I have tried
adding a third condition to the conditional formating:

If cell value is between 4 and -4 then no pattern and font
automatic

(I also tried between 3 and -3 and reversed them)
but the cell remains green. I guess the issue is around the "".

Many thanks for the help though.



"Ragdyer" wrote:

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.