T
tcazabon
I would like to add an item in a formula only if it is a negative number.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave F
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
In C1: =IF(A1<0,A1+B1,"")
where the set of double quotation double marks returns nothing.
Dave
B
Biff
=SUMIF(A1:A5,"<0")
Biff
Biff
E
Epinn
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
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
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
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
D
Dave F
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?
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?
E
Epinn
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.
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
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?
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.
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
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?
D
Dave F
Well, then I would have the formula return 0 and use the conditional
formatting suggestion. Then you can run your SUMPRODUCT with 0.
Dave
formatting suggestion. Then you can run your SUMPRODUCT with 0.
Dave
R
Roger Govier
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
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.
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
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?
But if you used
=SUMPRODUCT(--(E1:E2="yes"),C1:C2)
then it will work whether there are nulls or zero's
--
Regards
Roger Govier
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.
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
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?
E
Epinn
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/micro...ee3a5?lnk=st&q=&rnum=2&hl=en#f71049e47a1ee3a5
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
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
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.
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
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?
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/micro...ee3a5?lnk=st&q=&rnum=2&hl=en#f71049e47a1ee3a5
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
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
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.
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
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?