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.

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

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.

Roger,

Thank you so much. I had initiated a thread before I dropped by here. But, I have had no response and have been waiting for an answer. Your suggestion is what I have been waiting for. But I may still have a question on why yours work and the other two won't.

If you care to respond, this is my thread.

http://groups.google.ca/group/micros...49e47a 1ee3a5

http://tinyurl.com/y6m26b

So, I guess it is okay to use null, if we are not concerned about being consistent, right?

Okay, everyone is happy now.

Thanks.

Epinn

"Roger Govier" wrote in message ...
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.