Returning Zero
 

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell. Is
there a formula that will return a zero if either of the values in any of the
cells being subtracted is empty or a formula that would cover both situations?
Thanks
--
Silvercreek

Returning Zero
 

=IF(OR(A12=0,B12=0),0,MAX(A12-B12,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Silvercreek" wrote in message
...
I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell.
Is
there a formula that will return a zero if either of the values in any of
the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek

Returning Zero
 

Not sure if I get the question but try =IF(OR(A1=0,B1=0),0,A1-B1)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Silvercreek" wrote in message
...
I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell.
Is
there a formula that will return a zero if either of the values in any of
the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek

Returning Zero
 

I think I understand... You are using something like this formula

=MAX(A1-B1,0)

to return 0 or a positive number. But you want something that returns zero
if either cell is empty (or equal to 0). Try this...

=(A1-B1)*(A1<0)*(B1<0)

"Silvercreek" wrote:

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell. Is
there a formula that will return a zero if either of the values in any of the
cells being subtracted is empty or a formula that would cover both situations?
Thanks
--
Silvercreek

Returning Zero
 

This works for empty or zero cells, in Excel, that would return a positive
when subtracted from a positive cell, but not when positive cells are
subtracted from zero or empty. Can this be tweaked a bit?
Thanks for your help
--
Silvercreek


"Bob Phillips" wrote:

=IF(OR(A12=0,B12=0),0,MAX(A12-B12,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Silvercreek" wrote in message
...
I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell.
Is
there a formula that will return a zero if either of the values in any of
the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek

Returning Zero
 

This works for empty or zero cells, in Excel, that would return a positive
when subtracted from a positive cell, but not when positive cells are
subtracted from zero or empty. Can this be tweaked a bit?
Thanks for your help

--
Silvercreek


"Bernard Liengme" wrote:

Not sure if I get the question but try =IF(OR(A1=0,B1=0),0,A1-B1)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Silvercreek" wrote in message
...
I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell.
Is
there a formula that will return a zero if either of the values in any of
the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek

Returning Zero
 

I quote your original post ...

Is there a formula that will return a zero if either of the values in any of
the
cells being subtracted is empty

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Silvercreek" wrote in message
...
This works for empty or zero cells, in Excel, that would return a positive
when subtracted from a positive cell, but not when positive cells are
subtracted from zero or empty. Can this be tweaked a bit?
Thanks for your help
--
Silvercreek


"Bob Phillips" wrote:

=IF(OR(A12=0,B12=0),0,MAX(A12-B12,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Silvercreek" wrote in message
...
I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but
that
does not work when subtracting zero or empty cells from a positive
cell.
Is
there a formula that will return a zero if either of the values in any
of
the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek

Returning Zero
 

Your right, that's what I'm trying to do but the formula, unfortunately,
still doesn't work. Maybe I should explain in more detail. I was trying to
keep it simple but it doesn't seem like it's going to be that simple.
I'm working with dates.
Just for visualizations sake, I'm subtracting two cells to get the number
of days spent on a drawing on my project. I subtract the "start date" from
the "end date" and I get my answer. Simple. If the project isn't finished
but the drawing is then, of course, the "end date" for the project is empty.
If the "end date" is empty then "end date" minus "start date", (0-n), is less
than zero, that is handled with the MAX command, (thank you all very much).
But, if I enter "end date" with no "start date" (n-0), (which can happen in a
slightly different scenerio for me) then of course I will get a positive
return which is untrue.
The formulas I have been getting are helpfull but only work in one
situation or the other but not both. I have tried tweaking the formulas
being suggested but I haven't been able to make anything work. I hoper there
is something that will work.
Thanks for listening (reading).
--
Silvercreek


"Sloth" wrote:

I think I understand... You are using something like this formula

=MAX(A1-B1,0)

to return 0 or a positive number. But you want something that returns zero
if either cell is empty (or equal to 0). Try this...

=(A1-B1)*(A1<0)*(B1<0)

"Silvercreek" wrote:

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell. Is
there a formula that will return a zero if either of the values in any of the
cells being subtracted is empty or a formula that would cover both situations?
Thanks
--
Silvercreek

Returning Zero
 

thanks this is what i was looking for, very helpful

"Sloth" wrote:

I think I understand... You are using something like this formula

=MAX(A1-B1,0)

to return 0 or a positive number. But you want something that returns zero
if either cell is empty (or equal to 0). Try this...

=(A1-B1)*(A1<0)*(B1<0)

"Silvercreek" wrote:

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell. Is
there a formula that will return a zero if either of the values in any of the
cells being subtracted is empty or a formula that would cover both situations?
Thanks
--
Silvercreek

Returning Zero
 

Hi,

If the following formula is returning what you want:

=(A1-B1)*(A1<0)*(B1<0)

then you might try this formula:

=(A1-B1)*(A1*B1)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"captlb" wrote in message
...
thanks this is what i was looking for, very helpful

"Sloth" wrote:

I think I understand... You are using something like this formula

=MAX(A1-B1,0)

to return 0 or a positive number. But you want something that returns
zero
if either cell is empty (or equal to 0). Try this...

=(A1-B1)*(A1<0)*(B1<0)

"Silvercreek" wrote:

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but
that
does not work when subtracting zero or empty cells from a positive
cell. Is
there a formula that will return a zero if either of the values in any
of the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek

Returning Zero
 

Hi,

I need to correct the formula I just sent to read

=(A1-B1)*(A1*B1<0)

cheers,
Shane Devenshire

"captlb" wrote in message
...
thanks this is what i was looking for, very helpful

"Sloth" wrote:

I think I understand... You are using something like this formula

=MAX(A1-B1,0)

to return 0 or a positive number. But you want something that returns
zero
if either cell is empty (or equal to 0). Try this...

=(A1-B1)*(A1<0)*(B1<0)

"Silvercreek" wrote:

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but
that
does not work when subtracting zero or empty cells from a positive
cell. Is
there a formula that will return a zero if either of the values in any
of the
cells being subtracted is empty or a formula that would cover both
situations?
Thanks
--
Silvercreek