I would like to add an item in a formula only if it is a negative number.

Assume the number is in A1 and the number to which you're adding it is in B1.
In C1: =IF(A1<0,A1+B1,"")

where the set of double quotation double marks returns nothing.

Dave
--
Brevity is the soul of wit.


"tcazabon" wrote:

I would like to add an item in a formula only if it is a negative number.

I don't understand why "" and not 0.

So, C1 can be text (null is considered text) or numeric depending on the value of A1?

Is there a way that we can insert 0 to get the field consistent (i.e. numeric) but format it in such a way that 0 won't show?

Just wondering ......

Epinn

"Dave F" wrote in message ...
Assume the number is in A1 and the number to which you're adding it is in B1.
In C1: =IF(A1<0,A1+B1,"")

where the set of double quotation double marks returns nothing.

Dave
--
Brevity is the soul of wit.


"tcazabon" wrote:

I would like to add an item in a formula only if it is a negative number.

If you wanted to return 0 instead of nothing, and not have 0 show, then I
suppose you could apply conditional formatting such that the font color is
the same as the background color if the cell value is zero.

I'm still unclear, though, why you would want 0 to be returned instead of
nothing? Does zero have an advantage that nothing does not have?
--
Brevity is the soul of wit.


"Epinn" wrote:

I don't understand why "" and not 0.

So, C1 can be text (null is considered text) or numeric depending on the value of A1?

Is there a way that we can insert 0 to get the field consistent (i.e. numeric) but format it in such a way that 0 won't show?

Just wondering ......

Epinn

"Dave F" wrote in message ...
Assume the number is in A1 and the number to which you're adding it is in B1.
In C1: =IF(A1<0,A1+B1,"")

where the set of double quotation double marks returns nothing.

Dave
--
Brevity is the soul of wit.


"tcazabon" wrote:

I would like to add an item in a formula only if it is a negative number.

I'm still unclear, though, why you would want 0 to be returned instead of
nothing? <<

As I mentioned in my first post, I want the cell/column to be consistent i.e. numeric and not sometimes text and sometimes numeric depending on the value in column A.

I may be overly concerned, but I have a reason for that. May be you can help me get rid of my concern.

Does zero have an advantage that nothing does not have? <<

Yes. If I use zero, the following formulae won't fail. If I use "" and "" is returned the following formulae will fail.

=SUMPRODUCT((E1:E2="yes")*(C1:C2))

=SUMPRODUCT(--(E1:E2="yes"),--(C1:C2))

Of course, you can argue that I can fix my SUMPRODUCT formulae to check for "".

But I agree with you that "Brevity is the soul of wit" so I prefer to use 0 instead of checking for "" in the SUMPRODUCT formulae.

If it is not a problem to show 0, I'll definitely go with 0.

I am open to suggestions.

Epinn










"Dave F" wrote in message ...
If you wanted to return 0 instead of nothing, and not have 0 show, then I
suppose you could apply conditional formatting such that the font color is
the same as the background color if the cell value is zero.

I'm still unclear, though, why you would want 0 to be returned instead of
nothing? Does zero have an advantage that nothing does not have?
--
Brevity is the soul of wit.


"Epinn" wrote:

I don't understand why "" and not 0.

So, C1 can be text (null is considered text) or numeric depending on the value of A1?

Is there a way that we can insert 0 to get the field consistent (i.e. numeric) but format it in such a way that 0 won't show?

Just wondering ......

Epinn

"Dave F" wrote in message ...
Assume the number is in A1 and the number to which you're adding it is in B1.
In C1: =IF(A1<0,A1+B1,"")

where the set of double quotation double marks returns nothing.

Dave
--
Brevity is the soul of wit.


"tcazabon" wrote:

I would like to add an item in a formula only if it is a negative number.

Well, then I would have the formula return 0 and use the conditional
formatting suggestion. Then you can run your SUMPRODUCT with 0.

Dave
--
Brevity is the soul of wit.


"Epinn" wrote:

I'm still unclear, though, why you would want 0 to be returned instead of
nothing? <<

As I mentioned in my first post, I want the cell/column to be consistent i.e. numeric and not sometimes text and sometimes numeric depending on the value in column A.

I may be overly concerned, but I have a reason for that. May be you can help me get rid of my concern.

Does zero have an advantage that nothing does not have? <<

Yes. If I use zero, the following formulae won't fail. If I use "" and "" is returned the following formulae will fail.

=SUMPRODUCT((E1:E2="yes")*(C1:C2))

=SUMPRODUCT(--(E1:E2="yes"),--(C1:C2))

Of course, you can argue that I can fix my SUMPRODUCT formulae to check for "".

But I agree with you that "Brevity is the soul of wit" so I prefer to use 0 instead of checking for "" in the SUMPRODUCT formulae.

If it is not a problem to show 0, I'll definitely go with 0.

I am open to suggestions.

Epinn










"Dave F" wrote in message ...
If you wanted to return 0 instead of nothing, and not have 0 show, then I
suppose you could apply conditional formatting such that the font color is
the same as the background color if the cell value is zero.

I'm still unclear, though, why you would want 0 to be returned instead of
nothing? Does zero have an advantage that nothing does not have?
--
Brevity is the soul of wit.


"Epinn" wrote:

I don't understand why "" and not 0.

So, C1 can be text (null is considered text) or numeric depending on the value of A1?

Is there a way that we can insert 0 to get the field consistent (i.e. numeric) but format it in such a way that 0 won't show?

Just wondering ......

Epinn

"Dave F" wrote in message ...
Assume the number is in A1 and the number to which you're adding it is in B1.
In C1: =IF(A1<0,A1+B1,"")

where the set of double quotation double marks returns nothing.

Dave
--
Brevity is the soul of wit.


"tcazabon" wrote:

I would like to add an item in a formula only if it is a negative number.

Epinn

But if you used
=SUMPRODUCT(--(E1:E2="yes"),C1:C2)
then it will work whether there are nulls or zero's

--
Regards

Roger Govier


"Epinn" wrote in message
...
I'm still unclear, though, why you would want 0 to be returned
instead of
nothing? <<

As I mentioned in my first post, I want the cell/column to be consistent
i.e. numeric and not sometimes text and sometimes numeric depending on
the value in column A.

I may be overly concerned, but I have a reason for that. May be you can
help me get rid of my concern.

Does zero have an advantage that nothing does not have? <<

Yes. If I use zero, the following formulae won't fail. If I use "" and
"" is returned the following formulae will fail.

=SUMPRODUCT((E1:E2="yes")*(C1:C2))

=SUMPRODUCT(--(E1:E2="yes"),--(C1:C2))

Of course, you can argue that I can fix my SUMPRODUCT formulae to check
for "".

But I agree with you that "Brevity is the soul of wit" so I prefer to
use 0 instead of checking for "" in the SUMPRODUCT formulae.

If it is not a problem to show 0, I'll definitely go with 0.

I am open to suggestions.

Epinn










"Dave F" wrote in message
...
If you wanted to return 0 instead of nothing, and not have 0 show, then
I
suppose you could apply conditional formatting such that the font color
is
the same as the background color if the cell value is zero.

I'm still unclear, though, why you would want 0 to be returned instead
of
nothing? Does zero have an advantage that nothing does not have?
--
Brevity is the soul of wit.


"Epinn" wrote:

I don't understand why "" and not 0.

So, C1 can be text (null is considered text) or numeric depending on
the value of A1?

Is there a way that we can insert 0 to get the field consistent (i.e.
numeric) but format it in such a way that 0 won't show?

Just wondering ......

Epinn

"Dave F" wrote in message
...
Assume the number is in A1 and the number to which you're adding it is
in B1.
In C1: =IF(A1<0,A1+B1,"")

where the set of double quotation double marks returns nothing.

Dave
--
Brevity is the soul of wit.


"tcazabon" wrote:

I would like to add an item in a formula only if it is a negative
number.

=SUMIF(A1:A5,"<0")

Biff

"tcazabon" wrote in message
...
I would like to add an item in a formula only if it is a negative number.